Sql-server – Need some help in Group by StateID – TSQL Query

sql serversql server 2014t-sql

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.

Best Answer

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'

Related Solutions

SQL Server Query Refinement – How to Optimize T-SQL Queries

Add as many SUM(CASE as you need according selected dates.

SELECT      WoPart.PartNumber AS [Part Number]
           ,Parts.Descript AS [Description]
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 THEN Qty ELSE 0 END) AS '2014'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 THEN Qty ELSE 0 END) AS '2015'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 THEN Qty ELSE 0 END) AS '2016'
FROM       WoPart
INNER JOIN WorkOrders
ON         WorkOrders.RecordID = WoPart.RecordID
INNER JOIN Parts
ON         WoPart.PartNumber = Parts.PartNumber
WHERE      RelDateTime >= CAST('20140101' AS DATETIME)
AND        RelDateTime <  CAST('20170101' AS DATETIME)
GROUP BY   WoPart.PartNumber, Parts.Descript;

Sql-server – Need Help With SQL Server 2008 Join With 3 Tables

I've set up the tables as per your posted definitions (there wasn't one for sr_dep_codes so I created it with just the fields used in the query) and input some basic data into the tables - one DimDate entry that has a matching sr_assigned_to row and one that doesn't and using bottom version of the query (with the correct reference to ALT_YR_MONTH) it behaves as expected:

select          ALT_YR_MONTH as YR_MONTH,
                a.dep_code,
                a.div_code,
                d.dep_name,
                actions_assigned = isnull(count(action_id),0) 
from            DimDate 
Left outer join sr_assigned_to a 
on              (assign_date =[DATE] or assign_date is null)
Left outer join sr_dep_codes d 
on              (d.dep_code = a.dep_code or d.dep_code is null) 
and             (d.div_code = a.div_code or d.div_code is null)
where           [date] between DATEADD(m,-13,DATEADD(mm, DATEDIFF(m,0,GETDATE()), 0)) 
                       and  DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
group by        a.dep_code,
                a.div_code,
                d.dep_name, 
                ALT_YR_MONTH

Best Answer

Related Solutions

SQL Server Query Refinement – How to Optimize T-SQL Queries

Sql-server – Need Help With SQL Server 2008 Join With 3 Tables

Related Question