Sql aggregate functions

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 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

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