CODEWITHSUNDEEP
sqlsql-server
Yousef
Yousef

Reputation: 403

"SQL Case Then When" with accumulating the results

I have table with three column, first column is number of months, the second is name of a company, and the third one is the number of complains of that company. Some companies have no complains in some months, I want to generate a table, which one axis would be month axis and the other one would be the company name and the value of each cell of the table would be the number of complains.

Could you please help me with writing of this SQL Query? This is my try with pivot (with Jason's help, but it won't work, and return null. P.S. Numb is float, and Complaint and Months are nvarchar)

select *
from 
(
  select Numb, Complaint, Months
  from Rezayat94
) src
pivot
(
  sum(numb)
  for months in ([1], [2], [3],[4],[5],[6], [7], [8],[9],[10],[11],[12])
) piv;

Thanks. :)

Upvotes: 0

Views: 80

Answers (3)

P.Salmon
P.Salmon

Reputation: 17655

You don't have to use a pivot table.

DECLARE @COMPLAINTS TABLE ([MONTH] VARCHAR(3),COMPANY VARCHAR(1), NUMB FLOAT)
INSERT INTO @COMPLAINTS
VALUES 
('Jan','A',1),
('Feb','A',2),
('Mar','A',3),
('Apr','A',4),
('May','A',5),
('Jun','A',6),
('Jul','A',7),
('Aug','A',8),
('Sep','A',9),
('Oct','A',10),
('Nov','A',11),
('Dec','A',12),
('Jan','B',1),
('Feb','B',2),
('Mar','B',3),
('Apr','B',4),
('May','B',5),
('Jun','B',6),
('Jul','B',7),
('Aug','B',8),
('Sep','B',9),
('Oct','B',10),
('Nov','B',11),
('Dec','B',12),
('Jan','C',1),
--('Feb','C',2),
('Mar','C',3),
--('Apr','C',4),
('May','C',5),
--('Jun','C',6),
('Jul','C',7),
--('Aug','C',8),
('Sep','C',9),
--('Oct','C',10),
('Nov','C',11),
('Dec','C',12)

SELECT  COMPANY Company,
        sum(CASE WHEN [MONTH] = 'Jan' then numb else 0 end) as 'Jan',
        sum(CASE WHEN [MONTH] = 'Feb' then numb else 0 end) as 'Feb',
        sum(CASE WHEN [MONTH] = 'Mar' then numb else 0 end) as 'Mar',
        sum(CASE WHEN [MONTH] = 'Apr' then numb else 0 end) as 'Apr',   
        sum(CASE WHEN [MONTH] = 'May' then numb else 0 end) as 'May',
        sum(CASE WHEN [MONTH] = 'Jun' then numb else 0 end) as 'Jun',
        sum(CASE WHEN [MONTH] = 'Jul' then numb else 0 end) as 'Jul',
        sum(CASE WHEN [MONTH] = 'Aug' then numb else 0 end) as 'Aug',
        sum(CASE WHEN [MONTH] = 'Sep' then numb else 0 end) as 'Sep',
        sum(CASE WHEN [MONTH] = 'Oct' then numb else 0 end) as 'Oct',
        sum(CASE WHEN [MONTH] = 'Nov' then numb else 0 end) as 'Nov',
        sum(CASE WHEN [MONTH] = 'Dec' then numb else 0 end) as 'Dec'
FROM    @COMPLAINTS
GROUP   BY COMPANY

And the sum returns a 0 value for months where there are no complaints. The execution plan looks no more costly than a pivot (in this small test sample anyway)

Upvotes: 1

reza.cse08
reza.cse08

Reputation: 6178

Try this. Please inform if there is any problem.

select Numb, 
      Complaint,
      [1] AS Jan, 
      [2] as Feb, 
      [3] as Mar,
      [4] as Apr,
      [5] as May,
      [6] as Jun,
      [7] as Jul,
      [8] as Aug,
      [9] as Sep,
      [10] as Oct,
      [11] as Nov,
      [12] as Dec
from Rezayat94
pivot
(
  sum(numb)
  for months in ([1], [2], [3],[4],[5],[6], [7], [8],[9],[10],[11],[12])
) piv;

Upvotes: 1

Mitrucho
Mitrucho

Reputation: 136

for months in ([1], [2], [3],[4],[5],[6], [7], [8],[9],[10],[11],[12])

This will work only if a value "1","2","3" is stored in your Month column. You specified that it is nvarchar datatype for your column. So, please replace [1],[2]...etc with your values from month column.

For example you have Jan,Feb... etc. So please use something like this

for months in ([Jan], [Feb],... , [Dec])

Hope this will help

Upvotes: 0

Related Questions