Sql-server – Query To Show Quarterly Data

sql serversql-server-2008-r2t-sql

I am attempting to write a query to show data for two quarters. I have my query returning accurate data from my calculations, BUT it returns two rows of data instead of just a single row like I need. TO further show this, see this screenshot
Image

This is my sample DDL

Create Table Test
(
  saledate date
  ,value float
  ,admincharges1 float
  ,admincharges2 float
  ,admincharges3 float
  ,admincharges4 float
  ,admincharges5 float
  ,admincharges6 float
)

Insert Into Test 
(saledate, value, admincharges1, admincharges2, admincharges3, admincharges4, admincharges5, admincharges6) Values
('01/01/2016', 200, 10, 20, 10, 10, 10, 10)
,('06/01/2016', 400, 20, 20, 20, 20, 20, 20)

And this is the query that I use to try to get the "I want Returned" from my image above. 

Select
case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0))) 
end As Q1
,case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0))) 
end As Q2
FROM Test
Group by Datepart(Year, [saledate]), DATEPART(Quarter, [saledate])

What do I need to alter in my query in order to return my desired output?

Best Answer

You're getting two rows because your GROUP BY forces two rows of output, and your case statements run on each.

By putting the case statements inside the SUMs and removing the GROUP BY, you can return a single row.

I think below should work:

SELECT
SUM(
    case 
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q1
,SUM(
    case 
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q2
FROM Test
Related Question