Sql aggregate functions: Difference between revisions
From wikinotes
No edit summary |
(→COUNT) |
||
Line 10: | Line 10: | ||
count user_ids in each department in the 'department_users' table | count user_ids in each department in the 'department_users' table | ||
<syntaxhighlight lang="MySQL"> | <syntaxhighlight lang="MySQL"> | ||
SELECT COUNT(user_id) | SELECT department_id, COUNT(user_id) | ||
FROM department_users | FROM department_users | ||
GROUP BY department_id; | GROUP BY department_id; |
Revision as of 19:30, 19 September 2021
COUNT
count counts rows, either all results or for each
GROUP BY
group.count number of rows in users table
SELECT COUNT(*) FROM users;count user_ids in each department in the 'department_users' table
SELECT department_id, COUNT(user_id) FROM department_users GROUP BY department_id;You can also count the result of different conditions in the same table
Count total questions by anonymous/registered users.SELECT COUNT(anonamous_question_id) AS "Total Anonymous Questions", COUNT(registered_question_id) AS "Total Identified Questions" FROM ( SELECT anonamous_question_id, registered_question_id FROM database.questions LEFT JOIN ( SELECT id AS anonamous_question_id FROM database.questions WHERE asker = '' ) ON anonamous_question_ids = id LEFT JOIN ( SELECT id AS registered_question_id FROM database.questions WHERE asker != '' ) ON registered_question_id = id );
GROUP BY
TODO:
yuck. expand with count, how multiple columns specified in group by works
## only display the first result for each unique item_name ## SELECT item_name, item_desc FROM my_table GROUP BY item_name