Mysql count occurrence by group

Question

Hi i have following table on mysql DB.

╔═══════════╦═════════╦════════╦════════════════╗
║ REVIEW_ID ║ USER_ID ║ STATUS ║   DATE_ADDED   ║
╠═══════════╬═════════╬════════╬════════════════╣
║       218 ║       2 ║ cool   ║ 20130121134811 ║
║       218 ║       2 ║ cool   ║ 20130121134812 ║
║       218 ║       2 ║ lame   ║ 20130121134813 ║
║       218 ║       2 ║ funny  ║ 20130121134814 ║
║       218 ║       2 ║ funny  ║ 20130121134815 ║
║       218 ║       2 ║ funny  ║ 20130121134816 ║
║       218 ║       2 ║ lame   ║ 20130121134817 ║
╚═══════════╩═════════╩════════╩════════════════╝

how can i get a result where when i do query based on user_id i need to get result of total status for each type:

╔════════╦════════════╗
║ STATUS ║ TOTALCOUNT ║
╠════════╬════════════╣
║ cool   ║          2 ║
║ funny  ║          3 ║
║ lame   ║          2 ║
╚════════╩════════════╝

Thanks

If you google "Mysql count by group" the first hit gives you a very simple solution: http://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-count-with-group-by.php — Dan, Jan 23 '13 at 09:35

score 13 · Accepted Answer · edited Jan 23 '13 at 09:36

13

Use COUNT() which is an aggregate function, and group them according to their status

SELECT  status, COUNT(*) totalCount
FROM    tableName
GROUP   BY status

SQLFiddle Demo

OTHER(s)

MySQL GROUP BY with some Aggregate Functions List

edited Jan 23 '13 at 09:36

Dan

45,079
17
88
157

answered Jan 23 '13 at 09:32

John Woo

258,903
69
498
492

score 1 · Answer 2 · answered Jan 23 '13 at 09:33

1

SELECT status, count(*)
FROM your_table
GROUP BY status

answered Jan 23 '13 at 09:33

fthiella

48,073
15
90
106

+1 @fthiella otherwise nobody will vote our correct answers ;) – bonCodigo Jan 23 '13 at 09:36

score 1 · Answer 3 · answered Jan 23 '13 at 09:35

1

SELECT status, count(*)
FROM yourtable
GROUP BY status
;

answered Jan 23 '13 at 09:35

bonCodigo

14,268
1
48
91

Tsingyi · Answer 4 · 2013-01-23T10:58:10.367

0

SELECT  STATUS, COUNT(*) AS TOTALCOUNT
FROM    tableName
GROUP   BY STATUS
HAVING   USER_ID = user_id you need

edited Jan 23 '13 at 10:58

answered Jan 23 '13 at 10:44

Tsingyi

679
6
5

Mysql count occurrence by group

4 Answers4

Linked

Related