Adding values in a table in SQL 2008

Question

Hi all,

Trying to get a basic understanding of T-SQL here in SQL Server 2008. Suppose I have a table named "Issues" with columns such as:

Priority User
1        Foo
1        Foo
2        Foo
5        Foo
4        Bar
5        Bar
1        Bar
1        Fuz

and I wish to display a count of the Priority for each User, along with a breakdown of each Priority, such that the resulting table might be named "Breakdown" might look like

User Total 1 2 3 4 5
Foo  4     2 1 0 0 1
Bar  3     1 0 0 1 1
Fuz  1     1 0 0 0 0

I was thinking I might declare variables and write my query something like

DECLARE @P1 INT
DECLARE @P2 INT
DECLARE @P3 INT
DECLARE @P4 INT
DECLARE @P5 INT

SELECT COUNT(id) AS Total,UserName, 
CASE Priority
 WHEN 1 Then @P1 = @P1 + 1
 WHEN 2 Then @P2 = @P2 + 1
 WHEN 3 Then @P3 = @P3 + 1
 WHEN 4 Then @P4 = @P4 + 1
 WHEN 5 Then @P5 = @P5 + 1
END,
FROM Breakdown
GROUP BY UserName

but I'm pretty sure I'm on the wrong track. Does anyone have any suggestions?

Thanks, and sorry for the noobish question; but I'm not sure exactly what to google for here...

-R.

Answer 1

Use:

  SELECT i.user, 
         COUNT(i.priority) AS total,
         SUM(CASE WHEN i.priority = 1 THEN 1 ELSE 0 END) AS 1,
         SUM(CASE WHEN i.priority = 2 THEN 1 ELSE 0 END) AS 2,
         SUM(CASE WHEN i.priority = 3 THEN 1 ELSE 0 END) AS 3,
         SUM(CASE WHEN i.priority = 4 THEN 1 ELSE 0 END) AS 4,
         SUM(CASE WHEN i.priority = 5 THEN 1 ELSE 0 END) AS 5
    FROM ISSUES i
GROUP BY i.user

It's a pivot query, converting row data into columnar data.
Not a noob/beginner issue to deal with. SQL Server 2005+ added the (now ANSI) PIVOT/UNPIVOT syntax, but this is portable to most databases (because few currently support PIVOT/UNPIVOT).

Answer 2

You need to SELECT one column for each column you want in your result set. In your SQL, you're only selecting three columns. Try:

SELECT UserName,
       Count(*) AS Total, 
       SUM(CASE Priority WHEN 1 THEN 1 ELSE 0 END) AS P1_Total,
       SUM(CASE Priority WHEN 2 THEN 1 ELSE 0 END) AS P2_Total,
       SUM(CASE Priority WHEN 3 THEN 1 ELSE 0 END) AS P3_Total,
       SUM(CASE Priority WHEN 4 THEN 1 ELSE 0 END) AS P4_Total,
      SUM(CASE Priority WHEN 5 THEN 1 ELSE 0 END) AS P5_Total
FROM Issues 
GROUP BY UserName