CODEWITHSUNDEEP
mysql
trante
trante

Reputation: 33996

MySQL multiple COUNTs

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

Upvotes: 1

Views: 152

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

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;

Upvotes: 2

Fabio
Fabio

Reputation: 23510

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

Upvotes: 1

Related Questions