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: w3resource.com/mysql/aggregate-functions-and-grouping/… — Dan
– Dan, Commented Jan 23, 2013 at 9:35

Dan · Accepted Answer · 2013-01-23 09:36:47Z

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, 2013 at 9:36

Dan

45.8k20 gold badges98 silver badges167 bronze badges

answered Jan 23, 2013 at 9:32

John Woo

265k70 gold badges507 silver badges500 bronze badges

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

Comments

fthiella · Accepted Answer · 2013-01-23 09:33:30Z

1

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

answered Jan 23, 2013 at 9:33

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

1 Comment

bonCodigo Over a year ago

+1 @fthiella otherwise nobody will vote our correct answers ;)

bonCodigo · Accepted Answer · 2013-01-23 09:35:51Z

1

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

answered Jan 23, 2013 at 9:35

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Comments

Tsingyi · Accepted Answer · 2013-01-23 10:58:10Z

0

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

edited Jan 23, 2013 at 10:58

answered Jan 23, 2013 at 10:44

Tsingyi

7996 silver badges6 bronze badges

Collectives™ on Stack Overflow

Mysql count occurrence by group

4 Answers 4

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Linked

Related