Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Histogram

Explore how to create a histogram by categorizing continuous numeric values into labeled price ranges using CASE expressions in SQL. Understand the use of aggregate functions, GROUP BY, and ORDER BY clauses to summarize and organize data. This lesson also covers alternate methods such as derived tables, nested IFs, and generated columns for flexible histogram generation.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
/* Query to generate a histogram of product prices */
SELECT   CASE 
            WHEN Price BETWEEN 0 AND 50 THEN '0 - 50'
            WHEN Price BETWEEN 51 AND 100 THEN '51 - 100'
            WHEN Price BETWEEN 101 AND 500 THEN '101 - 500'
            WHEN Price BETWEEN 501 AND 1000 THEN '501 - 1000'
            ELSE '1001+'
         END AS PriceRange,
         COUNT(ProductID) AS ProductCount
FROM     Products
GROUP BY PriceRange
ORDER BY MIN(Price);

Explanation

The explanation of the solution code is given below:

  • Lines 2–7: The SELECT statement specifies the output columns for the query. The CASE expression categorizes product prices into defined ranges with labels.

  • Line 8: The AS PriceRange assigns an alias to the computed column for clarity after the CASE expression is finished with the keyword END.

  • Line 9: The COUNT() function calculates the number of products in each price range.

  • Line 10: The FROM Products clause specifies the table being queried.

  • Line 11: The GROUP BY PriceRange ...