Multiple Values (GROUP BY) Issue

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

General SQL Server Forums

New to SQL Server Programming

Multiple Values (GROUP BY) Issue

Author

Topic

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-19 : 17:23:37

OK, new problem today.

I have some subqueries that are setup as variables. Those are then compiled as part of a SELECT statement in a stored procedure. Here is the code in question:


SET @LTE24hrs     = (SELECT COUNT(*) FROM #turnTimes WHERE ([Turn Time Hours] <= 24))
SET @GT24LTE48hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 24 AND [Turn Time Hours] <= 48)
SET @GT48LTE72hrs = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 48 AND [Turn Time Hours] <= 72)
SET @GT72hrs      = (SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 72)

SELECT
	COUNT(*) AS [Num Jobs],
	AVG([Turn Time Hours]) AS [Average Turn Time Hours],
	AVG(Convert(decimal(10,2), [Turn Time Days])) AS [Average Turn Time Days],
	@LTE24hrs    AS [LTE24hrs],
	@GT24LTE48hrs AS [GT24LTE48hrs],
	@GT48LTE72hrs AS [GT48LTE72hrs],
	@GT72hrs   AS [GT72hrs],
	[Ticket Types] AS [Ticket Types]

FROM
	#turnTimes

GROUP BY [Ticket Types]

I need the values of LTE24hrs, GT24LTE48hrs, GT48LTE72hrs, and GT72hrs to honor the grouping by Ticket Types, but it's not. Here is what this procedure currently kicks out.

1 | 24.670000 | 3.000000 | 344 | 88 | 43 | 24 | Art Check
181 | 20.409723 | 1.994475 | 344 | 88 | 43 | 24 | Engineering
5 | 0.986000 | 1.200000 | 344 | 88 | 43 | 24 | Preflight
228 | 18.605438 | 1.921052 | 344 | 88 | 43 | 24 | Proofing
84 | 27.200833 | 2.154761 | 344 | 88 | 43 | 24 | Site Maintenance

As you can see the values for those 4 fields are coming out the same for each row, which they should not.

Any help would be appreciated!!

-T.

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-20 : 02:20:17

Hello,

You can try this:

SELECT
COUNT(*) AS [Num Jobs],
AVG([Turn Time Hours]) AS [Average Turn Time Hours],
AVG(Convert(decimal(10,2), [Turn Time Days])) AS [Average Turn Time Days],
(SELECT COUNT(*) FROM #turnTimes WHERE ([Turn Time Hours] <= 24) AND [Ticket Types]=tt.[Ticket Types]) AS [LTE24hrs],
(SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 24 AND [Turn Time Hours] <= 48 AND [Ticket Types]=tt.[Ticket Types]) AS [GT24LTE48hrs],
(SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 48 AND [Turn Time Hours] <= 72 AND [Ticket Types]=tt.[Ticket Types]) AS [GT72hrs],
(SELECT COUNT(*) FROM #turnTimes WHERE [Turn Time Hours] > 72 AND [Ticket Types]=tt.[Ticket Types]) AS [GT72hrs],
[Ticket Types] AS [Ticket Types]
FROM
#turnTimes tt
GROUP BY
[Ticket Types]

Best regards,

Devart Team

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-20 : 02:36:10

[code]
SELECT
COUNT(*) AS [Num Jobs],
AVG([Turn Time Hours]) AS [Average Turn Time Hours],
AVG(CONVERT(decimal(10,2), [Turn Time Days])) AS [Average Turn Time Days],
COUNT(CASE WHEN [Turn Time Hours] <= 24 THEN 1 END) AS [LTE24hrs],
COUNT(CASE WHEN [Turn Time Hours] > 24 AND [Turn Time Hours] <= 48 THEN 1 END) AS [GT24LTE48hrs],
COUNT(CASE WHEN [Turn Time Hours] > 48 AND [Turn Time Hours] <= 72 THEN 1 END) AS [GT48LTE72hrs],
COUNT(CASE WHEN [Turn Time Hours] > 72 THEN 1 END) AS [GT72hrs],
[Ticket Types] AS [Ticket Types]

FROM
#turnTimes

GROUP BY
[Ticket Types]

[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 03:35:55

quote:

Originally posted by khtan


SELECT
        COUNT(*) AS [Num Jobs],
        AVG([Turn Time Hours])                                                          AS [Average Turn Time Hours],
        AVG(CONVERT(decimal(10,2), [Turn Time Days]))                                   AS [Average Turn Time Days],
        COUNT(CASE WHEN [Turn Time Hours] <= 24 THEN 1 END)                             AS [LTE24hrs],
        COUNT(CASE WHEN [Turn Time Hours] > 24 AND [Turn Time Hours] <= 48 THEN 1 END)  AS [GT24LTE48hrs],
        COUNT(CASE WHEN [Turn Time Hours] > 48 AND [Turn Time Hours] <= 72 THEN 1 END)  AS [GT48LTE72hrs],
        COUNT(CASE WHEN [Turn Time Hours] > 72 THEN 1 END)                              AS [GT72hrs],
        [Ticket Types]                                                                  AS [Ticket Types]

FROM
        #turnTimes

GROUP BY 
        [Ticket Types]

KH
[spoiler]Time is always against us[/spoiler]

Use

sum(case when expression then 1 else 0 end)

instead of

count (case when expression then 1 end)

to avoid unneccessary warnings

Madhivanan

Failing to plan is Planning to fail

bluvoodoo7
Starting Member

7 Posts

Posted - 2010-07-20 : 09:06:56

These suggestions both worked!! Thanks for your help!!

-T.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources