1

I think it is my group by in this syntax that is skewing my desired result set. I ONLY want to see the data I am requesting, however my query returns all the data like this:

salesman    Q1 2015 Q1 2016
Blue    NULL    NULL 
Blue    NULL    NULL
Blue    200     NULL
Blue    300     NULL
Blue    NULL    400
Blue    NULL    600

What I (thought) my syntax would return would ONLY be the below

salesman    [Q1 2015]   [Q1 2016]
Blue          500          1000

This is my DDL (well garbage data, but gets point across)

Create Table Barnacles
(
  warrantypurchasedate date
  ,salesman varchar(500)
  ,itemprice float
)
Insert Into Barnacles (warrantypurchasedate, salesman, itemprice) Values
('01/01/2014', 'Blue', 100)
,('02/01/2014', 'Blue', 300)
,('01/01/2015', 'Blue', 200)
,('02/01/2015', 'Blue', 300)
,('01/01/2016', 'Blue', 400)
,('02/01/2016', 'Blue', 600)

This is the query syntax I attempted:

Select
salesman
,case when DATEPART(Year, warrantypurchasedate) = '2015' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2015]
,case when DATEPART(Year, warrantypurchasedate) = '2016' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2016]
FROM Barnacles
GROUP BY salesman, warrantypurchasedate

What should I alter in my query so that I only see the SUM() for Q1 15 and the SUM() for Q1 16 not a row returned for each entry in the table?

1

Untested, but I believe your query should look like:

SELECT salesman
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2015'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2015]
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2016'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2016]
FROM Barnacles
WHERE DATEPART(Year, warrantypurchasedate) in ('2015', '2016')
  AND DATEPART(Quarter, warrantypurchasedate) = '1' 
GROUP BY salesman

I moved sum outside of case, and added a WHERE clause to filter out relevent rows. I also removed date from the GROUP BY clause, otherwise you will get 1 row per date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.