Summarize aggregated data

Question

I have a table like as follows:

SoftwareName    Count    Country
Project         15       Canada
Visio           12       Canada
Project         10       USA
Visio           5        USA

How do I query it to give me a summary like...

SoftwareName    Canada    USA    Total
Project         15        10     25
Visio           12        5      17

How to do in T-SQL?

Answer 1

SELECT SoftwareName, 
  SUM( CASE Country WHEN 'Canada' THEN [Count] ELSE 0 END ) AS Canada,
  SUM( CASE Country WHEN 'USA'    THEN [Count] ELSE 0 END ) AS USA,
  SUM( [Count] ) AS Total
FROM [Table] 
GROUP BY SoftwareName;

Answer 2

This is called table pivoting. In your simple case, there are just two columns; in general, there could be 200 countries or so, in which case, the pivoting becomes rather hard.

There are many resources online describing how to do it: Google for 'pivot table sql'.

Answer 3

in SQL 2005 or later there-SQL keyword "Pivot" that does this for you, Check out the following link:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 4

OK...Here's how to do it using PIVOT:

SELECT Softwarename, Canada, USA, Canada + USA As TOTAL from SoftwareDemo 
PIVOT 
    (
     SUM([Count])
     FOR Country
     IN (Canada, USA)
    ) AS x


Softwarename                                       Canada      USA         TOTAL
-------------------------------------------------- ----------- ----------- -----------
Project                                            15          10          25
Visio                                              12          5           17

(2 row(s) affected)