Group by with SUM function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group by with SUM function

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-05-23 : 03:28:22

Hi,

I am trying to get the sum for some columns(Data1,Data2,Data3) for every hour for a number of days. OfficeA and OfficeZ is my unique key. Based on the key, I want to get the sum of columns(Data1,Data2,Data3)for all Hour between 8h00 to 23h00
I use this with no success.

SELECT
OfficeA ,
OfficeZ,
Heure,
case when CAST(Data1 AS Int) < 0 then -1 else  Sum(CAST(Data1 AS Int))  
End AS Data1,
case when CAST(Data2 AS Int) < 0 then -1 else  Sum(CAST(Data2 AS Int))  
End AS Data2,
case when CAST(Data2 AS Int) < 0 then 0 else  Sum(CAST(Data2 AS Int)) end  AS Data2

FROM tbl_Data 
GROUP BY
OfficeA ,
OfficeZ,
Heure,
case when CAST(Data1 AS Int) < 0 then -1 else  Sum(CAST(Data1 AS Int))  
End AS Data1,
case when CAST(Data2 AS Int) < 0 then -1 else  Sum(CAST(Data2 AS Int))  
End AS Data2,
case when CAST(Data2 AS Int) < 0 then 0 else  Sum(CAST(Data2 AS Int)) end  AS Data2
count([Day]) as [Number of Days]

Before
|OfficeA|OfficeZ|Day |Hour |Data1|Data2|Data3|
|091 |045 |Mon |08:00|24 |15 |18 |
|091 |045 |Tues|09:00|36 |12 |17 |
|091 |045 |Wed |10:00|24 |15 |18 |
|091 |045 |Thur|08:00|24 |15 |18 |
|091 |045 |Fri |08:00|24 |15 |18 |
|091 |045 |Mon |08:00|24 |15 |18 |
|091 |045 |Tues|08:00|24 |15 |18 |
|091 |045 |Wed |08:00|24 |15 |18 |
|091 |045 |Thur|08:00|24 |15 |18 |

Results I Wish to get for every hour between 08:00 and 23:00 for every combination of OfficeA and Office Z
|OfficeA|OfficeZ|Hour |Data1|Data2|Data3|Number of days|
|091 |045 |08:00|228 |132 |161 |9 |
|091 |045 |09:00|214 |134 |141 |9 |
|091 |045 |10:00|224 |124 |121 |9 |

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-23 : 10:30:22

[code]DECLARE @Sample TABLE
(
OfficeA CHAR(3) NOT NULL,
OfficeZ CHAR(3) NOT NULL,
[Day] VARCHAR(4) NOT NULL,
[Hour] TIME(0) NOT NULL,
Data1 INT NOT NULL,
Data2 INT NOT NULL,
Data3 INT NOT NULL
)

INSERT @Sample
VALUES ('091', '045', 'Mon' , '08:00', 24, 15, 18),
('091', '045', 'Tues', '09:00', 36, 12, 17),
('091', '045', 'Wed' , '10:00', 24, 15, 18),
('091', '045', 'Thur', '08:00', 24, 15, 18),
('091', '045', 'Fri' , '08:00', 24, 15, 18),
('091', '045', 'Mon' , '08:00', 24, 15, 18),
('091', '045', 'Tues', '08:00', 24, 15, 18),
('091', '045', 'Wed' , '08:00', 24, 15, 18),
('091', '045', 'Thur', '07:00', 24, 15, 18);

-- SwePeso
SELECT OfficeA,
OfficeZ,
[Hour],
SUM(CASE WHEN Data1 < 0 THEN -1 ELSE Data1 END) AS Data1,
SUM(CASE WHEN Data2 < 0 THEN -1 ELSE Data2 END) AS Data2,
SUM(CASE WHEN Data3 < 0 THEN -1 ELSE Data3 END) AS Data3,
COUNT(*) AS Days
FROM @Sample
WHERE [Hour] BETWEEN '08:00' AND '23:00'
GROUP BY OfficeA,
OfficeZ,
[Hour][/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources