Take the 2-minute tour ×

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?

share|improve this question

5 Answers 5

up vote 6 down vote accepted
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;
share|improve this answer
Thanks very much Bill. This totally solves it! I appreciate it. –  sammydc Oct 30 '08 at 1:48
Way better than PIVOT and makes total sense, Thank you. –  roman m Nov 3 '08 at 7:21

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

SELECT Softwarename, Canada, USA, Canada + USA As TOTAL from SoftwareDemo 
     FOR Country
     IN (Canada, USA)
    ) AS x

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

(2 row(s) affected)
share|improve this answer
Hi Marlon, I tried this solution too, and while I don't see any improvement in terms of execution time, I think this solution is very elegant and easy to modify later on. Thanks a lot. –  sammydc Oct 30 '08 at 18:44
With only the sample data as test load, there isn't much difference on their performance. I think this PIVOT is the preferred solution for this kind of data requirement. –  MarlonRibunal Oct 31 '08 at 7:30

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'.

share|improve this answer

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


share|improve this answer

i think you can use this Link :

Sum of unique records - better performance than a cursor

and i think using PIVOT Function have a best performance rater SUM() function.!

share|improve this answer

Your Answer


