Help with Grouping - 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 2005 Forums

Transact-SQL (2005)

Help with Grouping

Author

Topic

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 10:53:07

I'm having trouble getting the grouping correct to produce the results I need. Currently I'm getting the following.

Day Hour Sales_North Sales_South Sales_East Totals
----------------------------------------------------------------------
Sun 12 AM 4 1 0 5
Sun 12 PM 1087 876 1246 3209
Sun 4 AM 0 0 0 0
Sun 4PM 789 563 1011 2363
Sun 8 AM 87 76 118 281
Sun 8 PM 321 533 786 1640
Mon 12 AM 1 1 7 9
Mon 12 PM 982 653 991 2626
Mon 4 AM 0 1 0 1
Mon 4 PM 456 237 109 802
Mon 8 AM 104 400 842 1346
Mon 8 PM 198 217 176 591

What I need to get are the following results.

Day Hour Sales_North Sales_South Sales_East Totals
----------------------------------------------------------------------
Sun 12 AM 4 1 0 5
Sun 4 AM 0 0 0 0
Sun 8 AM 87 76 118 281
Sun 12 PM 982 653 991 2626
Sun 4 PM 789 563 1011 2363
Sun 8 PM 321 533 786 1640
Mon 12 AM 1 1 7 9
Mon 4 AM 0 1 0 1
Mon 8 AM 104 400 842 1346
Mon 12 PM 798 542 1382 2722
Mon 4 PM 456 237 109 802
Mon 8 PM 198 217 176 591

Here is my current sql.

SELECT 
	LEFT([Day],3) AS [Day],
	[Hour],
	AVG([Total Sales Sales_North]) AS [Sales_North],
	AVG([Total Sales Sales_South]) AS [Sales_South],
	AVG([Total Sales Sales_East]) AS [Sales.East],
	AVG([Total Sales Sales_North]) + AVG([Total Sales Sales_South]) + AVG([Total Sales Sales_East]) AS [Total]
	
FROM 
	(
		SELECT 
			DATEPART(weekday,[TimeStamp]) AS [DayNo],
			DATENAME(weekday,[TimeStamp]) AS [Day],
			CASE DATEPART(hour, [TimeStamp])
				WHEN 0 THEN '12 AM'
				WHEN 4 THEN '4 AM'
				WHEN 8 THEN '8 AM'
				WHEN 12 THEN '12 PM'
				WHEN 16 THEN '4 PM'
				WHEN 20 THEN '8 PM'
			END	AS [Hour],
			COUNT([TimeStamp]) AS [Total Sales Sales_North],
			CAST(NULL AS int) AS [Total Sales Sales_South],
			CAST(NULL AS int) AS [Total Sales Sales_East]
		FROM 
			Sales.Sales_North
		WHERE 
			[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
			AND
			DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
		GROUP BY 
			DATEPART(weekday,[TimeStamp]), 
			DATENAME(weekday,[TimeStamp]),
			DATEPART(hour, [TimeStamp])		
		UNION ALL
		SELECT DATEPART(weekday,[TimeStamp]),
			DATENAME(weekday,[TimeStamp]),
			CASE DATEPART(hour, [TimeStamp])
				WHEN 0 THEN '12 AM'
				WHEN 4 THEN '4 AM'
				WHEN 8 THEN '8 AM'
				WHEN 12 THEN '12 PM'
				WHEN 16 THEN '4 PM'
				WHEN 20 THEN '8 PM'
			END	AS [Hour],
			NULL, 
			COUNT([TimeStamp]),
			NULL
			
		FROM 
			Sales.Sales_South
		WHERE 
			[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
			AND
			DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
		GROUP BY 
			DATEPART(weekday,[TimeStamp]), 
			DATENAME(weekday,[TimeStamp]),
			DATEPART(hour, [TimeStamp])
		UNION ALL
		SELECT 
			DATEPART(weekday,[TimeStamp]),
			DATENAME(weekday,[TimeStamp]),
			CASE DATEPART(hour, [TimeStamp])
				WHEN 0 THEN '12 AM'
				WHEN 4 THEN '4 AM'
				WHEN 8 THEN '8 AM'
				WHEN 12 THEN '12 PM'
				WHEN 16 THEN '4 PM'
				WHEN 20 THEN '8 PM'
			END	AS [Hour],
			NULL,
			NULL, 
			COUNT([TimeStamp]) 
		FROM 
			Sales.Sales_East
		WHERE 
			[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
			AND
			DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
		GROUP BY 
			DATEPART(weekday,[TimeStamp]), 
			DATENAME(weekday,[TimeStamp]),
			DATEPART(hour, [TimeStamp])
	)t
	
GROUP BY [DayNo], [Day], [Hour]
ORDER BY [DayNo], [Day], [Hour]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:00:39

can you explain how the values for Sales on 12 PM changed? whats the rule for calculating them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 11:18:21

Sorry about that. It was a copy-paste error in reconstructing the resultant table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:46:44

if thats the case i cant see any difference in output so didnt understand whats the issue you're facing. I see only order changing but you shouldnt be worried at that as order in which records are stored doesnt matter at all as you can always retrieve them in the order you want
in this case just use TimeStamp field for ordering rather than using Day,hour etc separately

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 11:57:50

Thanks for your reply. The ordering is what I'm trying to get correct, sorry if I confused you by saying grouping. The output is used for a chart, so I when the chart is displayed I need to have the chart displayed like below.

Day Hour Sales_North Sales_South Sales_East Totals
----------------------------------------------------------------------
Sun 12 AM 4 1 0 5
Sun 4 AM 0 0 0 0
Sun 8 AM 87 76 118 281
Sun 12 PM 1087 876 1246 3209
Sun 4PM 789 563 1011 2363
Sun 8 PM 321 533 786 1640
Mon 12 AM 1 1 7 9
Mon 4 AM 0 1 0 1
Mon 8 AM 104 400 842 1346
Mon 12 PM 982 653 991 2626
Mon 4 PM 456 237 109 802
Mon 8 PM 198 217 176 591

So rather than the results being displayed where for Monday it is currently displaying as 12AM, 12PM, 4AM, 4PM, etc.. I need to have it display the results for Monday as well as the other days like 12AM, 4AM, 8AM, 12PM, 4PM, 8PM.

Does that make sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:04:15

thats why i told you need to use TimeStamp for sorting. just use

ORDER BY TimeStamp

and dont include it in select list and you'll get results you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 12:09:41

Thanks, but I keep getting:

Msg 207, Level 16, State 1, Line 90
Invalid column name 'TimeStamp'.

Here is the updated SQL as you suggested.

SELECT
LEFT([Day],3) AS [Day],
[Hour],
AVG([Total Volume M_Active]) AS [Paid],
AVG([Total Volume M_Reject]) AS [Reject],
AVG([Total Volume M_Void]) AS [Void],
AVG([Total Volume M_Active]) + AVG([Total Volume M_Reject]) + AVG([Total Volume M_Void]) AS [Total]

FROM
(
SELECT
DATEPART(weekday,[TimeStamp]) AS [DayNo],
DATENAME(weekday,[TimeStamp]) AS [Day],
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END AS [Hour],
COUNT([TimeStamp]) AS [Total Volume M_Active],
CAST(NULL AS int) AS [Total Volume M_Reject],
CAST(NULL AS int) AS [Total Volume M_Void]
FROM
CLAIMS.M_Active
WHERE
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
--ORDER BY DATENAME(weekday,[TimeStamp]),DATEPART(hour, [TimeStamp])
UNION ALL
SELECT DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END AS [Hour],
NULL,
COUNT([TimeStamp]),
NULL

FROM
CLAIMS.M_Reject
WHERE
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
UNION ALL
SELECT
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END AS [Hour],
NULL,
NULL,
COUNT([TimeStamp])
FROM
CLAIMS.M_Void
WHERE
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
)t

GROUP BY [DayNo], [Day], [Hour]
ORDER BY [TimeStamp]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:12:25

you need to include it in inside table and then use it.


SELECT 
LEFT([Day],3) AS [Day],
[Hour],
AVG([Total Volume M_Active]) AS [Paid],
AVG([Total Volume M_Reject]) AS [Reject],
AVG([Total Volume M_Void]) AS [Void],
AVG([Total Volume M_Active]) + AVG([Total Volume M_Reject]) + AVG([Total Volume M_Void]) AS [Total]

FROM 
(
SELECT [TimeStamp],
DATEPART(weekday,[TimeStamp]) AS [DayNo],
DATENAME(weekday,[TimeStamp]) AS [Day],
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
COUNT([TimeStamp]) AS [Total Volume M_Active],
CAST(NULL AS int) AS [Total Volume M_Reject],
CAST(NULL AS int) AS [Total Volume M_Void]
FROM 
CLAIMS.M_Active
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY [TimeStamp],
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
--ORDER BY DATENAME(weekday,[TimeStamp]),DATEPART(hour, [TimeStamp])
UNION ALL
SELECT [TimeStamp],
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
NULL, 
COUNT([TimeStamp]),
NULL

FROM 
CLAIMS.M_Reject
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY [TimeStamp],
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
UNION ALL
SELECT [TimeStamp],
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
NULL,
NULL, 
COUNT([TimeStamp]) 
FROM 
CLAIMS.M_Void
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY [TimeStamp],
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
)t

GROUP BY [DayNo], [Day], [Hour]
ORDER BY [TimeStamp]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 12:46:24

Thank you for your help. I used your modification, but it gives the following error.

Msg 8127, Level 16, State 1, Line 2
Column "t.TimeStamp" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

I then added [TimeStamp] in the last Group By clause to resolve the error; however, it gives way to many results. I should for each day only get back 6 rows (e.g. Sunday - 12am, 4am, 8am, 12pm, 4pm, 8pm).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:57:52

what about this?


SELECT 
LEFT([Day],3) AS [Day],
[Hour],
AVG([Total Volume M_Active]) AS [Paid],
AVG([Total Volume M_Reject]) AS [Reject],
AVG([Total Volume M_Void]) AS [Void],
AVG([Total Volume M_Active]) + AVG([Total Volume M_Reject]) + AVG([Total Volume M_Void]) AS [Total]

FROM 
(
SELECT 
DATEPART(weekday,[TimeStamp]) AS [DayNo],
DATENAME(weekday,[TimeStamp]) AS [Day],
DATEPART(hour, [TimeStamp]) AS [HourVal],
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
COUNT([TimeStamp]) AS [Total Volume M_Active],
CAST(NULL AS int) AS [Total Volume M_Reject],
CAST(NULL AS int) AS [Total Volume M_Void]
FROM 
CLAIMS.M_Active
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY 
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
--ORDER BY DATENAME(weekday,[TimeStamp]),DATEPART(hour, [TimeStamp])
UNION ALL
SELECT DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp]) AS [HourVal],
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
NULL, 
COUNT([TimeStamp]),
NULL

FROM 
CLAIMS.M_Reject
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY 
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
UNION ALL
SELECT 
DATEPART(weekday,[TimeStamp]),
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp]) AS [HourVal],
CASE DATEPART(hour, [TimeStamp])
WHEN 0 THEN '12 AM'
WHEN 4 THEN '4 AM'
WHEN 8 THEN '8 AM'
WHEN 12 THEN '12 PM'
WHEN 16 THEN '4 PM'
WHEN 20 THEN '8 PM'
END	AS [Hour],
NULL,
NULL, 
COUNT([TimeStamp]) 
FROM 
CLAIMS.M_Void
WHERE 
[TimeStamp] BETWEEN CAST('2011-06-19 00:00:00.000' AS DATETIME) AND CAST('2011-06-25 23:59:59.999' AS DATETIME)
AND
DATEPART(hour, [TimeStamp]) in (0, 4, 8, 12, 16, 20)
GROUP BY 
DATEPART(weekday,[TimeStamp]), 
DATENAME(weekday,[TimeStamp]),
DATEPART(hour, [TimeStamp])
)t

GROUP BY [DayNo], [Day],[HourVal], [Hour]
ORDER BY [DayNo],[HourVal]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jhorten2011
Starting Member

14 Posts

Posted - 2012-01-16 : 13:08:42

That is exactly what I was trying to get!!

Thank you so much!!! I've been racking my brain over this and the answer just wasn't clear to me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 13:32:06

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources