MySQL按组计数 [英] Mysql count occurrence by group

问题描述

我在mysql DB上有下表.

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 ║
╚═══════════╩═════════╩════════╩════════════════╝

如何获取结果，当我基于user_id进行查询时，我需要获取每种类型的总状态结果:

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 ║
╚════════╩════════════╝

谢谢

推荐答案

使用COUNT()这是一个聚合函数，并根据其status

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

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

• SQLFiddle演示
• SQLFiddle Demo

OTHER

OTHER(s)

• 带有某些汇总功能列表的MySQL GROUP BY

这篇关于MySQL按组计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！