Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Creating Views with Aggregate Functions

Explore the process of creating SQL views that summarize data using aggregate functions like SUM and COUNT. Understand how to join tables, group data effectively, and use aliases to simplify queries. This lesson also covers alternative methods including CTEs and subqueries for data summarization without views.

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.

  • ...