CODEWITHSUNDEEP
mysqlsqlselect
Saraswathi Apavoo
Saraswathi Apavoo

Reputation: 371

Mysql count occurrence by group

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

Upvotes: 6

Views: 5536

Answers (4)

Tsingyi
Tsingyi

Reputation: 799

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

Upvotes: 0

John Woo
John Woo

Reputation: 263693

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

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

OTHER(s)

Upvotes: 13

bonCodigo
bonCodigo

Reputation: 14361

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

Upvotes: 1

fthiella
fthiella

Reputation: 49049

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

Upvotes: 1

Related Questions