SQL Interview Preparation—Intermediate Level/

...

/

Answer: Creating Views with Aggregate Functions

Answer: Creating Views with Aggregate Functions

Find a detailed explanation of creating views with aggregate functions in SQL.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to create a view named MonthlyCategorySalesPerformance
to monitor performance per month of each category */
CREATE VIEW MonthlyCategorySalesPerformance AS
SELECT      S.Month AS 'Month',
            PC.CategoryName AS 'Category Name',
            SUM(S.SalesAmount) AS 'Total Sales Amount'
FROM        Sales S
JOIN        ProductCategories PC ON S.CategoryID = PC.CategoryID
GROUP BY    S.Month, PC.CategoryName;
SELECT * FROM MonthlyCategorySalesPerformance;

Code explanation

The explanation of the solution code is given below:

  • Line 3: The CREATE VIEW statement creates a view named MonthlyCategorySalesPerformance.

  • Lines 4–6: The columns of the view are Month, Category Name, and the calculated column Total Sales Amount.

  • Line 7: The data is retrieved from the Sales table.

  • Line 8: The JOIN is performed with Sales and ProductCategories on the columns CategoryID in both the tables.

  • Line 9: The result of the JOIN is grouped by Month and CategoryName.

  • Line 11: The SELECT statement displays the content of the created view.

Recalling relevant concepts

We have covered the following concepts in this question: ...