Sql aggregate functions

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