0

Can anyone help me on below scenario I need small help on group by state ID

Below is my query and I am tried to get all the out put in single line .

But I am getting different lines for each year . please help me to get all the output in single row as the State ID is same.

SELECT  StateID,
SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
(Select count(Fileid) where year = '2016' ) AS TotalFs2016,
SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
(Select count(Fileid) where year = '2017' ) AS TotalFs2017,
SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
(Select count(Fileid) where year = '2018' ) AS TotalFs2018,
SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
(Select count(Fileid) where year = '2019' ) AS TotalFs2019
--FileState
FROM
(
SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
FROM #Temp1
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp2
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp3
UNION ALL
SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
FROM #Temp4
)t 
where StateID like '570' 
GROUP BY 
t.StateID , year 

Current out put gives 4 rows for different years like 2016 , 2017 , 2018 , 2019 along with the Size of the files.

Where are I want to get only one row as put with all years data as the ID is same.

I tried to GROUP BY but still does not help .

Thank you for the help.

1
  • 2
    Wrap each output field (except StateID) with MAX() function. Remove yearfrom ORDER BY.
    – Akina
    Aug 28, 2018 at 8:34

1 Answer 1

1

Group by isn't needed since you only select one stateid

SELECT  StateID,
  SUM(CASE WHEN [Year] = 2016 THEN FileSize END) AS FileSize_2016,
  count(case when year = 2016 then Fileid end ) AS TotalFs2016,
  SUM(CASE WHEN [Year] = 2017 THEN FileSize END) AS FileSize_2017,
  count(case when year = 2017 then Fileid end ) AS TotalFs2017,
  SUM(CASE WHEN [Year] = 2018 THEN FileSize END) AS FileSize_2018,
  count(case when year = 2018 then Fileid end ) AS TotalFs2018,
  SUM(CASE WHEN [Year] = 2019 THEN FileSize END) AS FileSize_2019,
  count(case when year = 2018 then Fileid end ) AS TotalFs2019
--FileState
FROM
(
  SELECT StateID,[Year],FileID,FileTotalSizeMBs AS FileSize  --, FileState
  FROM #Temp1
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp2
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp3
  UNION ALL
  SELECT StateID,[Year],FileID,FileTotalSizeMBs --, FileState
  FROM #Temp4
)t
where t.StateID like '570'
2
  • 1
    You should read about the pivot queries, they are made for problems like these. I believe SQL Server has them. Aug 28, 2018 at 9:00
  • That's a great advice , I will definitely read and go through them . Thanks again. Aug 28, 2018 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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