Sql aggregate functions: Difference between revisions

From wikinotes
 
(2 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.


= Counting Results =
count number of rows in users table
<syntaxhighlight lang="mysql">
<syntaxhighlight lang="MySQL">
# Create Two columns: projectID,
SELECT COUNT(*) FROM users;
# and (#) of instances of projectID
SELECT DISTINCT project_Id, COUNT(*)  
FROM userDepartmentTable
GROUP BY project_Id;
</syntaxhighlight>
</syntaxhighlight>


= Count Multiple Columns from same Table =
count user_ids in each department in the 'department_users' table
Counting different conditions using the same source table.
<syntaxhighlight lang="MySQL">
SELECT department_id, COUNT(user_id)
FROM department_users
GROUP BY department_id;
</syntaxhighlight>


* use of ids indicates unique columns ''(otherwise cannot tell anon_id from joined id without value)''
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"


<source lang="bash">
FROM (
SELECT COUNT(anon_id) AS "Total Anonymous Questions",
   SELECT anonamous_question_id,
      COUNT(pub_id) AS "Total Identified Questions"
        registered_question_id
FROM  
(
   SELECT asker, anon_id, pub_id
   FROM database.questions
   FROM database.questions
   LEFT JOIN (
   LEFT JOIN (
     SELECT id AS anon_id
     SELECT id AS anonamous_question_id
     FROM database.questions
     FROM database.questions
     WHERE asker = ''
     WHERE asker = ''
   ) ON anon_id = id
   ) ON anonamous_question_ids = id
 
   LEFT JOIN (
   LEFT JOIN (
     SELECT id AS pub_id
     SELECT id AS registered_question_id
     FROM database.questions
     FROM database.questions
     WHERE asker != ''
     WHERE asker != ''
   ) ON pub_id = id
   ) ON registered_question_id = id
);
);
</source>
</syntaxhighlight>
 
</blockquote><!-- COUNT -->
== GROUP BY ==
{{ TODO |
yuck. expand with count, how multiple columns specified in group by works }}


<syntaxhighlight lang="mySQL">
= GROUP BY =
## only display the first result for each unique item_name
<blockquote>
##
Select one row per unique department_id/user_id combo
SELECT item_name,
<syntaxhighlight lang="MySQL">
      item_desc
SELECT department_id, user_id
FROM my_table
FROM department_users
GROUP BY item_name
GROUP BY department_id, user_id;
</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;
Retrieved from "http://willpittman.net:8080/index.php?title=Sql_aggregate_functions&oldid=27641"