Sql aggregate functions: Difference between revisions
From wikinotes
m (Will moved page Sql: aggregate functions to Sql aggregate functions without leaving a redirect) |
|
(No difference)
|
Revision as of 18:35, 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
);
CONCAT
SELECT * from my_table CONCAT(';', 'firstName', 'lastName') # concatenate first+lastname using the token ';' as separator in results
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