Sql aggregate functions: Difference between revisions
From wikinotes
(→COUNT) |
|||
Line 43: | Line 43: | ||
= GROUP BY = | = GROUP BY = | ||
<blockquote> | <blockquote> | ||
Select one row per unique department_id/user_id combo | |||
<syntaxhighlight lang="MySQL"> | |||
SELECT department_id, user_id | |||
<syntaxhighlight lang=" | FROM department_users | ||
GROUP BY department_id, user_id; | |||
SELECT | |||
FROM | |||
GROUP BY | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- GROUP BY --> | </blockquote><!-- GROUP BY --> |
Latest revision as of 19:32, 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
Select one row per unique department_id/user_id combo
SELECT department_id, user_id FROM department_users GROUP BY department_id, user_id;