Group by multiple criteria in one select

June 6, 2023

Expected
Using group by and case I was able to get the result of each product statistics for one interval. But I want the result for each interval horiziontally (above image), noting that using the following query we can get the result vertically:

 SELECT 
  p.category,
  (CASE
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 1 AND 5 THEN '1-5'
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 6 AND 9 THEN '6-9'
    WHEN TIMESTAMPDIFF(YEAR, p.dt, CURDATE()) BETWEEN 10 AND 15 THEN '10-15'
    ELSE '16-20'
  END) as Interval,
  COUNT(*) AS totalNbr
FROM product p  
GROUP BY p.category, Interval

I have tried using union but it is not working yet.

>Solution :

Use conditional aggregation. In MySQL:

SELECT category,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN  1 AND  5) AS diff_1_5,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN  6 AND  9) AS diff_6_9,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) BETWEEN 10 AND 15) AS diff_10_15,
    SUM(TIMESTAMPDIFF(YEAR, dt, CURRENT_DATE) >= 16)             AS diff_16_plus
FROM product
GROUP BY category