问题描述:

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

网友答案:

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;
网友答案:

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

相关阅读:
Top