How to display total of values in the same table as a row (in case statement)?
How to display total of values in the same table as a row (in case statement)?
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!
You could union
– P.Salmon
Sep 6 '18 at 6:09
Case expression. (Returns a value.)
– jarlh
Sep 6 '18 at 6:21
I much nicer way of dealing with ranges in sql can be found dba.stackexchange.com/questions/68791/group-by-in-ranges
– P.Salmon
Sep 6 '18 at 7:44
2 Answers
2
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;
thanks! It works. But it displays: NULL | 306. How to change on "total' instead of word 'NULL'?
– Елисей Горьков
Sep 6 '18 at 6:15
@ЕлисейГорьков updated my answer, check it out. Do not forget to accept it as the correct answer if it helped you. Happy coding! :)
– Stanislovas Kalašnikovas
Sep 6 '18 at 6:17
ERROR 1221 (HY000): Incorrect usage of CUBE/ROLLUP and ORDER BY
– Елисей Горьков
Sep 6 '18 at 6:55
@ЕлисейГорьков updated an answer, check it out.
– Stanislovas Kalašnikovas
Sep 6 '18 at 6:56
thanks, but...ERROR 1305 (42000): FUNCTION businessanalysis.GROUPING does not exist
– Елисей Горьков
Sep 6 '18 at 7:01
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
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
what is your sample data ?
– Madhur Bhaiya
Sep 6 '18 at 6:01