Sql aggregate functions: Difference between revisions
From wikinotes
m (Will moved page Sql: aggregate functions to Sql aggregate functions without leaving a redirect) |
|||
(3 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
= COUNT = | |||
<blockquote> | |||
count counts rows, either all results or for each <code>GROUP BY</code> group. | |||
count number of rows in users table | |||
<syntaxhighlight lang=" | <syntaxhighlight lang="MySQL"> | ||
SELECT COUNT(*) FROM users; | |||
SELECT | |||
FROM | |||
</syntaxhighlight> | </syntaxhighlight> | ||
= | count user_ids in each department in the 'department_users' table | ||
<syntaxhighlight lang="MySQL"> | |||
SELECT department_id, COUNT(user_id) | |||
FROM department_users | |||
GROUP BY department_id; | |||
</syntaxhighlight> | |||
You can also count the result of different conditions in the same table<br> | |||
Count total questions by anonymous/registered users. | |||
<syntaxhighlight lang="MySQL"> | |||
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 | |||
( | |||
SELECT | |||
FROM database.questions | FROM database.questions | ||
LEFT JOIN ( | LEFT JOIN ( | ||
SELECT id AS | SELECT id AS anonamous_question_id | ||
FROM database.questions | FROM database.questions | ||
WHERE asker = '' | WHERE asker = '' | ||
) ON | ) ON anonamous_question_ids = id | ||
LEFT JOIN ( | LEFT JOIN ( | ||
SELECT id AS | SELECT id AS registered_question_id | ||
FROM database.questions | FROM database.questions | ||
WHERE asker != '' | WHERE asker != '' | ||
) ON | ) ON registered_question_id = id | ||
); | ); | ||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- COUNT --> | |||
= GROUP BY = | |||
<blockquote> | |||
Select one row per unique department_id/user_id combo | |||
<syntaxhighlight lang="MySQL"> | |||
<syntaxhighlight lang=" | SELECT department_id, user_id | ||
FROM department_users | |||
GROUP BY department_id, user_id; | |||
SELECT | |||
FROM | |||
GROUP BY | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</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;