Creating histogram in SQL using Case... When

To create a histogram using the CASE WHEN concept in SQL, you can use the CASE statement to categorize data into bins and then aggregate the counts for each bin. Here's an example:

Let's say you have a table called sales with a column named amount that contains different sales amounts. You want to create a histogram to display the count of sales amounts falling into different ranges.

Here's an example query using the CASE WHEN statement to create a histogram:

SELECT
  bin_category,
  COUNT(*) AS bin_count
FROM
  (
    SELECT
      amount,
      CASE
        WHEN amount <= 1000 THEN '0-1000'
        WHEN amount > 1000 AND amount <= 5000 THEN '1001-5000'
        WHEN amount > 5000 THEN '5001+'
        ELSE 'Unknown'
      END AS bin_category
    FROM
      sales
  ) AS bin_data
GROUP BY
  bin_category
ORDER BY
  bin_category;

In this query, we first use the CASE statement to categorize the sales amounts into different bins based on the specified conditions. We assign the appropriate bin category label to each amount. We also include an "Unknown" category to handle any values that do not meet the specified conditions.

Next, we wrap the above query in a subquery (bin_data) to assign the bin category labels to each sales amount.

Finally, we use the outer query to aggregate the counts for each bin category using the GROUP BY clause. We count the occurrences of each bin category and assign it as bin_count.

The result of the query will include the bin_category column representing the bin ranges and the corresponding bin_count column displaying the count of sales amounts in each bin. The result will be ordered by the bin_category.

You can adjust the conditions and bin labels based on your specific requirements to create the desired histogram.