MySQL UNION ALL SELECT Issue on getting all the SUM per column

Question

I wish to get the SUM of the product per column, not sure if the UNION ALL SELECT can handle the desired result considering that the first column is concatenated.

Here is the code:

                 SUM(product.product_id = 1) AS Soda,
                 SUM(product.product_id = 2) AS Liquor,
                 SUM(product.product_id = 3) AS Lemon,
                 SUM(product.product_id = 4) AS Mango,
                 SUM(product.product_id = 5) AS Inhaler,
                 SUM(1) AS Count

FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT    0 lowest,  500 highest UNION
       SELECT  501   , 1000             UNION
       SELECT 1001   , 1500             UNION
       SELECT 1501   , 2000             UNION
       SELECT 2001   , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lowest AND ranges.highest
GROUP BY ranges.lowest, ranges.highest

UNION ALL SELECT '','','','','','',
(
    SELECT 
    COUNT(product.price * line_item.quantity)
    FROM (line_item
    INNER JOIN product ON line_item.product_id = product.product_id)
);

**The output:**
+-------------+------+--------+-------+-------+---------+-------+
| Revenue     | Soda | Liquor | Lemon | Mango | Inhaler | Count |
+-------------+------+--------+-------+-------+---------+-------+
| 0 - 500     | 4    | 0      | 4     | 0     | 1       |     9 |
| 501 - 1000  | 0    | 0      | 0     | 2     | 0       |     2 |
| 1001 - 1500 | 0    | 1      | 0     | 2     | 2       |     5 |
| 1501 - 2000 | 0    | 2      | 0     | 0     | 1       |     3 |
| 2001 - 2500 | 0    | 1      | 0     | 0     | 0       |     1 |
|             |      |        |       |       |         |    20 |
+-------------+------+--------+-------+-------+---------+-------+

Thank for your help.

score 0 · Answer 1 · answered Nov 22 '20 at 09:56

0

Have you tried ROLL UP operator? Seems like there's a similar problem to yours: Add a summary row with totals

answered Nov 22 '20 at 09:56

kasper

92
1
6

1

I tried. It actually works but it gives me a NULL value and reorders the revenue. – toff Nov 22 '20 at 10:00
1

Instead of posting an answer which merely links to another answer, please instead [flag the question](/help/privileges/flag-posts) as a duplicate. – Sabito stands with Ukraine Nov 22 '20 at 12:12

score 0 · Answer 2 · answered Nov 22 '20 at 13:42

I'm confused. You are defining the ranges already. If you want a range that encompasses all the values, just add it in:

FROM line_item JOIn
     product
     USING (product_id) JOIN
     ( SELECT    0 lowest,  500 highest UNION ALL
       SELECT  501   , 1000             UNION ALL
       SELECT 1001   , 1500             UNION ALL
       SELECT 1501   , 2000             UNION ALL
       SELECT 2001   , 2500             UNION ALL
       SELECT    0   , 2500             
-------^ all encompassing range
     ) ranges
     ON product.price * line_item.quantity BETWEEN ranges.lowest AND ranges.highest

Voila! This also has the summary row.

MySQL UNION ALL SELECT Issue on getting all the SUM per column

2 Answers2