How to display total of values in the same table as a row (in case statement)?

user_10296392 0
Viewed 63

I have got a query:

SELECT t.diapason, COUNT(distinct user_id) AS 'number_of_users'
    FROM (SELECT p.user_id, p.amount as total, CASE  
       when amount<=100 then '0-100' 
       when amount>100 and amount<=150 then '100-150' 
       when amount>150 then '>150 +' END AS diapason
       FROM 
         (SELECT payments.user_id, SUM(amount) AS amount 
         FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity 
          where login_time between '2018-04-12' and '2018-04-18') a ON 
          payments.user_id = a.user_id
          GROUP BY payments.user_id) p) t
GROUP BY t.diapason
ORDER BY number_of_users desc;

What should I change to display total of number_of_users as a row?

Output

+----------+-----------------+
| diapason | number_of_users |
+----------+-----------------+
| 0-100    |             278 |
| >150 +   |              16 |
| 100-150  |              12 |
| total    |             306 |    - I need this row
+----------+-----------------+

Thanks!

select join mysql sql database
2 Answers

Take a look at ROLLUP, something like:

SELECT IF(GROUPING(t.diapason), 'total', t.diapason) AS diapason,
       COUNT(distinct user_id) AS 'number_of_users'
FROM (SELECT p.user_id, p.amount as total, CASE  
   when amount<=100 then '0-100' 
   when amount>100 and amount<=150 then '100-150' 
   when amount>150 then '>150 +' END AS diapason
   FROM 
     (SELECT payments.user_id, SUM(amount) AS amount 
     FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity 
      where login_time between '2018-04-12' and '2018-04-18') a ON 
      payments.user_id = a.user_id
      GROUP BY payments.user_id) p) t
GROUP BY t.diapason WITH ROLLUP;
user_46093600

There's already a good answer here but just in case you cannot use ROLLUP you can use UNION:

SELECT t.diapason, COUNT(distinct user_id) AS 'number_of_users'
    FROM (SELECT p.user_id, p.amount as total, CASE  
       when amount<=100 then '0-100' 
       when amount>100 and amount<=150 then '100-150' 
       when amount>150 then '>150 +' END AS diapason
       FROM 
         (SELECT payments.user_id, SUM(amount) AS amount 
         FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity 
          where login_time between '2018-04-12' and '2018-04-18') a ON 
          payments.user_id = a.user_id
          GROUP BY payments.user_id) p) t
GROUP BY t.diapason
UNION ALL
SELECT 'total' AS diapason, COUNT(distinct user_id) AS 'number_of_users'
    FROM (SELECT p.user_id, p.amount as total, CASE  
       when amount<=100 then '0-100' 
       when amount>100 and amount<=150 then '100-150' 
       when amount>150 then '>150 +' END AS diapason
       FROM 
         (SELECT payments.user_id, SUM(amount) AS amount 
         FROM payments INNER JOIN (SELECT DISTINCT user_id FROM activity 
          where login_time between '2018-04-12' and '2018-04-18') a ON 
          payments.user_id = a.user_id
          GROUP BY payments.user_id) p) t
GROUP BY 1
ORDER BY number_of_users desc;

Hope it helps

user_71670060
Related