# 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

• 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 Friday, January 25, 2013 10:57 PM
• Marked as answer by 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 Friday, January 25, 2013 10:57 PM
• Marked as answer by 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