Summing Aggregated Data

Question

I have a table like the following:

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

How do I request it to give me a resume, for example ...

  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

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) 

Answer 3

This is called table rotation. In your simple case, there are only two columns; in general, there may be 200 countries or so, in which case the turn becomes quite difficult.

There are many resources on the Internet that describe how to do this: Google for the "sql pivot table".

Answer 4

in SQL 2005 or later, the SQL keyword “Pivot” that does this for you. Check the following link:

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

Answer 5

I think you can use this link:

Sum of unique records - better performance than cursor

and I think using the PIVOT function has a better SUM () function with a performance function.!