How to get multiple counts with one SQL query

Question

I am wondering how to write this query   I know this actual syntax is bogus  but it will help you understand what I am wanting  I need it in this format  because it is part of a much bigger query   SELECT distributor id   COUNT    AS TOTAL   COUNT    WHERE level    exec    COUNT    WHERE level    personal    I need this all returned in one query    Also  it need to be in one row  so the following won t work    SELECT distributor id  COUNT    GROUP BY distributor id

User · Answer

Based on Bluefeet s accepted response with an added nuance using OVER     SELECT distributor id      COUNT    total      SUM case when level    exec  then 1 else 0 end  OVER   ExecCount      SUM case when level    personal  then 1 else 0 end  OVER    PersonalCount FROM yourtable GROUP BY distributor id   Using OVER   with nothing in the    will give you the total count for the whole dataset

User · Answer

I think this can also works for you select count    as anc  select count    from Patient where sex  F  as patientF  select count    from Patient where sex  M   as patientM from anc  and also you can select and count related tables like this select count    as anc  select count    from Patient where Patient Id anc PatientId as patientF  select count    from Patient where sex  M   as patientM from anc

User · Answer

One way which works for sure  SELECT a distributor id       SELECT COUNT    FROM myTable WHERE level  personal  and distributor id   a distributor id  as PersonalCount       SELECT COUNT    FROM myTable WHERE level  exec  and distributor id   a distributor id  as ExecCount       SELECT COUNT    FROM myTable WHERE distributor id   a distributor id  as TotalCount FROM  SELECT DISTINCT distributor id FROM myTable  a     EDIT   See  KevinBalmforth s break down of performance for why you likely don t want to use this method and instead should opt for  Taryn  s answer   I m leaving this so people can understand their options

User · Answer

Well  if you must have it all in one query  you could do a union   SELECT distributor id  COUNT   FROM     UNION SELECT COUNT   AS EXEC COUNT FROM     WHERE level    exec  UNION SELECT COUNT    AS PERSONAL COUNT FROM     WHERE level    personal     Or  if you can do after processing   SELECT distributor id  COUNT    FROM     GROUP BY level    You will get the count for each level and need to sum them all up to get the total

User · Answer

For MySQL  this can be shortened to   SELECT distributor id      COUNT    total      SUM level    exec   ExecCount      SUM level    personal   PersonalCount FROM yourtable GROUP BY distributor id

User · Answer

Building on other posted answers   Both of these will produce the right values   select distributor id      count    total      sum case when level    exec  then 1 else 0 end  ExecCount      sum case when level    personal  then 1 else 0 end  PersonalCount from yourtable group by distributor id  SELECT a distributor id             SELECT COUNT    FROM myTable WHERE level  personal  and distributor id   a distributor id  as PersonalCount             SELECT COUNT    FROM myTable WHERE level  exec  and distributor id   a distributor id  as ExecCount             SELECT COUNT    FROM myTable WHERE distributor id   a distributor id  as TotalCount        FROM myTable a      However  the performance is quite different  which will obviously be more relevant as the quantity of data grows    I found that  assuming no indexes were defined on the table  the query using the SUMs would do a single table scan  while the query with the COUNTs would do multiple table scans   As an example  run the following script   IF OBJECT ID  N t1   N U   IS NOT NULL  drop table t1  create table t1  f1 int        insert into t1 values  1       insert into t1 values  1       insert into t1 values  2      insert into t1 values  2      insert into t1 values  2      insert into t1 values  3      insert into t1 values  3      insert into t1 values  3      insert into t1 values  3      insert into t1 values  4      insert into t1 values  4      insert into t1 values  4      insert into t1 values  4      insert into t1 values  4    SELECT SUM CASE WHEN f1   1 THEN 1 else 0 end   SUM CASE WHEN f1   2 THEN 1 else 0 end   SUM CASE WHEN f1   3 THEN 1 else 0 end   SUM CASE WHEN f1   4 THEN 1 else 0 end  from t1  SELECT   select COUNT    from t1 where f1   1    select COUNT    from t1 where f1   2    select COUNT    from t1 where f1   3    select COUNT    from t1 where f1   4    Highlight the 2 SELECT statements and click on the Display Estimated Execution Plan icon  You will see that the first statement will do one table scan and the second will do 4  Obviously one table scan is better than 4   Adding a clustered index is also interesting  E g   Create clustered index t1f1 on t1 f1   Update Statistics t1    The first SELECT above will do a single Clustered Index Scan  The second SELECT will do 4 Clustered Index Seeks  but they are still more expensive than a single Clustered Index Scan  I tried the same thing on a table with 8 million rows and the second SELECT was still a lot more expensive

User · Answer

I do something like this where I just give each table a string name to identify it in column A  and a count for column  Then I union them all so they stack  The result is pretty in my opinion - not sure how efficient it is compared to other options but it got me what I needed   select  table1   count     from table1 union select  table2   count     from table2 union select  table3   count     from table3 union select  table4   count     from table4 union select  table5   count     from table5 union select  table6   count     from table6 union select  table7   count     from table7    Result   -------------------   String    Count   -------------------   table1    123       table2    234       table3    345       table4    456       table5    567     -------------------

User · Answer

SELECT      distributor id       COUNT    AS TOTAL       COUNT IF level  exec  1 null        COUNT IF level  personal  1 null   FROM sometable    COUNT only counts non null values and the DECODE will return non null value 1 only if your condition is satisfied

User · Answer

You can use a CASE statement with an aggregate function  This is basically the same thing as a PIVOT function in some RDBMS   SELECT distributor id      count    AS total      sum case when level    exec  then 1 else 0 end  AS ExecCount      sum case when level    personal  then 1 else 0 end  AS PersonalCount FROM yourtable GROUP BY distributor id

[mysql] How to get multiple counts with one SQL query?

Examples related to mysql

Examples related to sql

Examples related to join

Examples related to count

Examples related to group-by