MySQL multiple COUNTs

Question

I have a table like this:

Fiddle: http://sqlfiddle.com/#!2/44d9e/14

CREATE TABLE IF NOT EXISTS `mytable` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(20) NOT NULL,
  `money_earned` int(20) NOT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO mytable (user_id,money_earned) VALUES ("111","10");
INSERT INTO mytable (user_id,money_earned) VALUES ("111","6");
INSERT INTO mytable (user_id,money_earned) VALUES ("111","40");
INSERT INTO mytable (user_id,money_earned) VALUES ("222","45");
INSERT INTO mytable (user_id,money_earned) VALUES ("222","1");
INSERT INTO mytable (user_id,money_earned) VALUES ("333","5");
INSERT INTO mytable (user_id,money_earned) VALUES ("333","19");

I need to know table has how many rows, how many different users, and how many times each user has earned.

I need this result:

TOTAL_ROWS: 7
TOTAL_INDIVIDUAL_USERS: 3

USER_ID USER_TIMES  
111     3       
222     2       
333     2

That's just SUM(money_earned) with the same GROUP BY. Looks like you are overthinking it. — Michael Berkowski
– Michael Berkowski, Commented Jun 6, 2013 at 21:12
The 0 you got initially is due to MySQL casting the string literal 'times' to an integer value. Since it is non-numeric, it casts to 0. — Michael Berkowski
– Michael Berkowski, Commented Jun 6, 2013 at 21:14

Gordon Linoff · Accepted Answer · 2013-06-06 21:30:41Z

2

Is your problem that you want the total as well? If so, then you can get this using rollup:

SELECT coalesce(cast(user_id as char(20)), 'TOTAL USER_TIMES'),
      COUNT(*) as times
FROM mytable
GROUP BY user_id with rollup;

You can get the user counts in a separate column with this trick:

SELECT coalesce(cast(user_id as char(20)), 'TOTAL USER_TIMES'),
      COUNT(*) as times, count(distinct user_id) as UserCount
FROM mytable
GROUP BY user_id with rollup;

You realize that a SQL query just returns a table of values. You are asking for very specific formatting, which is typically done better at the application level. That said, you can get close to what you want with something like this:

select user, times
from ((SELECT 3 as ord, cast(user_id as char(20)) as user, COUNT(*) as times
       FROM mytable
       GROUP BY user_id
      )
      union all
      (select 1, 'Total User Count', count(*)
       from mytable
      )
      union all
      (select 2, 'Total Users', count(distinct user_id)
       from mytable
      )
     ) t
order by ord;

edited Jun 6, 2013 at 21:30

answered Jun 6, 2013 at 21:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Orbling Over a year ago

Amazing how little knowledge there is of WITH ROLLUP.

trante Over a year ago

Thank you very much for your effort.

Fabio · Accepted Answer · 2013-06-06 21:20:21Z

1

I think this could be a typo anyway your are trying to sum your COUNT() times, simply replace with money_earned

SELECT user_id, 
COUNT(*) AS 'times', 
SUM(money_earned) AS 'sum_money'
FROM mytable GROUP BY user_id;

SQL Fiddle

edited Jun 6, 2013 at 21:20

answered Jun 6, 2013 at 21:14

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

2 Comments

trante Over a year ago

I'm sorry for my mistake I wrote the question wrong, now I edited the question.

Fabio Over a year ago

@trante i think Gordon answered your actual question, my answer was exactly for the previous question

Collectives™ on Stack Overflow

MySQL multiple COUNTs

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related