Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Get several sums in one row

Answered Get several sums in one row

  • Friday, January 25, 2013 10:15 PM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Friday, January 25, 2013 10:21 PM
     
      Has Code
    Sign In to Vote
    0

    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:32 PM
     
      Has Code
    Sign In to Vote
    0

    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:39 PM
     
     
    Sign In to Vote
    0
    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:57 PM
     
     Answered
    Sign In to Vote
    1

    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 11:19 PM
     
     Answered
    Sign In to Vote
    0

    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