Sql aggregate functions: Difference between revisions
From wikinotes
m (Will moved page Sql: aggregate functions to Sql aggregate functions without leaving a redirect) |
(→CONCAT) |
||
Line 33: | Line 33: | ||
); | ); | ||
</source> | </source> | ||
== GROUP BY == | == GROUP BY == |
Revision as of 19:19, 19 September 2021
Counting Results
# Create Two columns: projectID,
# and (#) of instances of projectID
SELECT DISTINCT project_Id, COUNT(*)
FROM userDepartmentTable
GROUP BY project_Id;
Count Multiple Columns from same Table
Counting different conditions using the same source table.
- use of ids indicates unique columns (otherwise cannot tell anon_id from joined id without value)
SELECT COUNT(anon_id) AS "Total Anonymous Questions",
COUNT(pub_id) AS "Total Identified Questions"
FROM
(
SELECT asker, anon_id, pub_id
FROM database.questions
LEFT JOIN (
SELECT id AS anon_id
FROM database.questions
WHERE asker = ''
) ON anon_id = id
LEFT JOIN (
SELECT id AS pub_id
FROM database.questions
WHERE asker != ''
) ON pub_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