none
Get several sums in one row

    Question

  • I have something like this:

    select count(*) as 'total 1' from table where value = 1
    select count(*) as 'total 2' from table where value = 2
    select count(*) as 'total 3' from table where value = 3

    what would i need to do so that the resultset is one row with three columns, each column having one sum?

    So it would return something like this:

    'total 1'     'total 2'     'total 3'
      80             90            85


    Thanks.


    VM

    Friday, January 25, 2013 10:15 PM

Answers

  • This is a common idiom in SQL.

    SELECT grouping_column,
           SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) AS val_tot_1,
           SUM(CASE WHEN val = 2 THEN 1 ELSE 0 END) AS val_tot_2,
           SUM(CASE WHEN val = 3 THEN 1 ELSE 0 END) AS val_tot_3
    FROM Foobar
    WHERE ..
    GROUP BY grouping_column;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by dgjohnson Friday, January 25, 2013 10:57 PM
    • Marked as answer by Iric WenModerator Monday, February 04, 2013 9:12 AM
    Friday, January 25, 2013 10:57 PM
  • SELECT SUM(CASE value WHEN 1 THEN 1 ELSE 0 END) AS [total 1],
           SUM(CASE value WHEN 2 THEN 1 ELSE 0 END) AS [total 2],
           SUM(CASE value WHEN 3 THEN 1 ELSE 0 END) AS [total 3]
    FROM   tbl

    This is the typical pattern of pivoting something. (And you don't need the PIVOT keyword for that.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 11:19 PM

All replies

  • Just put them in one statement:

    Select	SomeField
    		,(select count(*) from table where value = 1) as 'total 1'
    		,(select count(*) from table where value = 2) as 'total 2'
    		,(select count(*) from table where value = 3) as 'total 3'
    From	MyTable
    Where	Something = Thisotherthing

    Friday, January 25, 2013 10:21 PM
  • Thanks.

    I know it can be done that way, the reason I ask is that I've seen queries that look like the following, and I just wanted to know how that works, with so many joins that aren't related.

    select a.Total,
    b.F1,c.F2 from 
    (select count(*) as Total from 
    (select distinct Site from table where (field='a value') 
    group by AField) aa) a  join 
    (select COUNT(*) as F1 from table where 
    field = 'another field') b on 1=1 join 
    (select COUNT(*) as F2  from another_table where 
    field = 'yet another val') c on 1=1...
    and so on.



    VM

    Friday, January 25, 2013 10:32 PM
  • You're just joining to derived tables.  Each of the subqueries essentially creates a table on the fly, it is aliased as whatever, and used as part of the query.  When you use the on 1=1 it is the same as a cross join.  I'm not sure about the performance, but the subquery method is more readable for me than the derived table method.
    Friday, January 25, 2013 10:39 PM
  • This is a common idiom in SQL.

    SELECT grouping_column,
           SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) AS val_tot_1,
           SUM(CASE WHEN val = 2 THEN 1 ELSE 0 END) AS val_tot_2,
           SUM(CASE WHEN val = 3 THEN 1 ELSE 0 END) AS val_tot_3
    FROM Foobar
    WHERE ..
    GROUP BY grouping_column;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by dgjohnson Friday, January 25, 2013 10:57 PM
    • Marked as answer by Iric WenModerator Monday, February 04, 2013 9:12 AM
    Friday, January 25, 2013 10:57 PM
  • SELECT SUM(CASE value WHEN 1 THEN 1 ELSE 0 END) AS [total 1],
           SUM(CASE value WHEN 2 THEN 1 ELSE 0 END) AS [total 2],
           SUM(CASE value WHEN 3 THEN 1 ELSE 0 END) AS [total 3]
    FROM   tbl

    This is the typical pattern of pivoting something. (And you don't need the PIVOT keyword for that.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 11:19 PM