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)

Grouping?

Author

Topic

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-18 : 14:49:09

I have a query which is pretty simple if we just ignore the basic functions and operators in it:

SELECT ((( SUM(AgentStatistics.DurCallsAnswPriACD)
+ SUM(AgentStatistics.DurCallsAnswPriCTI)
+ SUM(AgentStatistics.DurCallsAnswOverACD)
+ SUM(AgentStatistics.DurCallsAnswOverCTI)
)+ SUM(AgentStatistics.DurCallsInternalReceived)
-(( SUM(AgentStatistics.DurCallsInternalReceived)
- SUM(AgentStatistics.DurCallsConstInternalRcvd)
- SUM(AgentStatistics.DurCallsTransInternalRcvd)
- SUM(AgentStatistics.DurCallsConfInternalRcvd)
)))/((( SUM(AgentStatistics.CallsAnswPriACD)
+ SUM(AgentStatistics.CallsAnswOverACD)
+ SUM(AgentStatistics.CallsAnswPriCTI)
+ SUM(AgentStatistics.CallsAnswOverCTI)
)+ SUM(AgentStatistics.CallsInternalReceived)
-(( SUM(AgentStatistics.CallsInternalReceived)
- SUM(AgentStatistics.CallsConstInternalRcvd)
)))+(( SUM(AgentStatistics.CallsAnswPriACDStart_Range)
+ SUM(AgentStatistics.CallsAnswOverACDStart_Range)
+ SUM(AgentStatistics.CallsAnswPriCTIStart_Range)
+ SUM(AgentStatistics.CallsAnswOverCTIStart_Range)
)+ SUM(AgentStatistics.CallsInternalReceivedStart_Range)
-( SUM(AgentStatistics.CallsInternalReceivedStart_Range)
- SUM(AgentStatistics.CallsConstInternalRcvdStart_Range)
))))
FROM   (("CallCenter1"."dbo"."AgentStatistics" "AgentStatistics"
	INNER JOIN "CallCenter1"."dbo"."Agent" "Agent" ON "AgentStatistics"."AgentID"="Agent"."AgentId")
	INNER JOIN "CallCenter1"."dbo"."AgentMap" "AgentMap" ON "Agent"."AgentIdentity"="AgentMap"."AgentIdentity")
	INNER JOIN "CallCenter1"."dbo"."AgentFolder" "AgentFolder" ON "AgentMap"."AgentFolderIdentity"="AgentFolder"."AgentFolderIdentity"
	WHERE ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
			AND "AgentStatistics"."ReportDayDate" < GETDATE())
		AND ("AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'}
			OR "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'})
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()))
			OR ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()))
			AND "AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'})
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE())
			OR ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE())
			AND "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'})
		AND "AgentMap"."AgentFolderIdentity"=9

It just calculates the rate of an entire "team" in the organization. Now I need to get that rate but for each "agent" and order the results by the returned rate, the fields are

"Agent"."AgentName", "AgentStatistics"."AgentID"

I'm guessing that I can do that by grouping everything by "AgentID", but I have no idea how to accomplish the task.
Any suggestions? Being as descriptive as possible would help, since I'm just a newbie.

There are 10 kinds of people: those who understand binary and those who don't

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-18 : 15:51:35

Have you tried this:

select Agent, AgentName, AgentStatistics, AgentID, <then your nasty aggregate expression>
From <your existing from clause>
GROUP BY Agent, AgentName, AgentStatistics, AgentID

Be One with the Optimizer
TG

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-19 : 17:53:46

quote:
Originally posted by TG

Have you tried this:

select Agent, AgentName, AgentStatistics, AgentID, <then your nasty aggregate expression>
From <your existing from clause>
GROUP BY Agent, AgentName, AgentStatistics, AgentID

Be One with the Optimizer
TG

I've tried it, but since my nasty aggregate expression only returns a single number, adding the <SELECT "Agent"."AgentId"> and <GROUP BY "Agent"."AgentId"> gives me an error: "Could not find stored procedure 'Agent.AgentId'" or I also get a "Divide by zero error encountered"

There are 10 kinds of people: those who understand binary and those who don't

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-19 : 19:44:26

well, the devide by zero error can be handled by either a CASE statement or wrap the devisor in a NullIF function.
The other error just means your syntax was wrong. Post the code you've got with the Group by included.

Also, what is this "{ts '1900-01-01 11:00:00'}"? Is this MS Sql Server?

Be One with the Optimizer
TG

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-22 : 16:42:44

This is the query that produces a "Divide by zero" error. I'm trying to do the grouping, but I'm not sure of how to do it... I could ignore rows whose results <= 0


SELECT ((( SUM(AgentStatistics.DurCallsAnswPriACD)
+ SUM(AgentStatistics.DurCallsAnswPriCTI)
+ SUM(AgentStatistics.DurCallsAnswOverACD)
+ SUM(AgentStatistics.DurCallsAnswOverCTI)
)+ SUM(AgentStatistics.DurCallsInternalReceived)
-(( SUM(AgentStatistics.DurCallsInternalReceived)
- SUM(AgentStatistics.DurCallsConstInternalRcvd)
- SUM(AgentStatistics.DurCallsTransInternalRcvd)
- SUM(AgentStatistics.DurCallsConfInternalRcvd)
)))/((( SUM(AgentStatistics.CallsAnswPriACD)
+ SUM(AgentStatistics.CallsAnswOverACD)
+ SUM(AgentStatistics.CallsAnswPriCTI)
+ SUM(AgentStatistics.CallsAnswOverCTI)
)+ SUM(AgentStatistics.CallsInternalReceived)
-(( SUM(AgentStatistics.CallsInternalReceived)
- SUM(AgentStatistics.CallsConstInternalRcvd)
)))+(( SUM(AgentStatistics.CallsAnswPriACDStart_Range)
+ SUM(AgentStatistics.CallsAnswOverACDStart_Range)
+ SUM(AgentStatistics.CallsAnswPriCTIStart_Range)
+ SUM(AgentStatistics.CallsAnswOverCTIStart_Range)
)+ SUM(AgentStatistics.CallsInternalReceivedStart_Range)
-( SUM(AgentStatistics.CallsInternalReceivedStart_Range)
- SUM(AgentStatistics.CallsConstInternalRcvdStart_Range)
))))
FROM   (("CallCenter1"."dbo"."AgentStatistics" "AgentStatistics"
	INNER JOIN "CallCenter1"."dbo"."Agent" "Agent" ON "AgentStatistics"."AgentID"="Agent"."AgentId")
	INNER JOIN "CallCenter1"."dbo"."AgentMap" "AgentMap" ON "Agent"."AgentIdentity"="AgentMap"."AgentIdentity")
	INNER JOIN "CallCenter1"."dbo"."AgentFolder" "AgentFolder" ON "AgentMap"."AgentFolderIdentity"="AgentFolder"."AgentFolderIdentity"
	WHERE ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
			AND "AgentStatistics"."ReportDayDate" < GETDATE() )
		AND ("AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'}
			OR "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			OR ("AgentStatistics"."ReportDayDate" >= {ts '2008-09-17 00:00:00'}
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			AND "AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			OR ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			AND "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND "AgentMap"."AgentFolderIdentity" = 9
	GROUP BY "Agent"."AgentId"

There are 10 kinds of people: those who understand binary and those who don't

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-22 : 16:46:36

You have to use Nullif or case statement in denominator as TG suggested.

quote:

Originally posted by h4x0rmx

This is the query that produces a "Divide by zero" error. I'm trying to do the grouping, but I'm not sure of how to do it... I could ignore rows whose results <= 0


SELECT ((( SUM(AgentStatistics.DurCallsAnswPriACD)
+ SUM(AgentStatistics.DurCallsAnswPriCTI)
+ SUM(AgentStatistics.DurCallsAnswOverACD)
+ SUM(AgentStatistics.DurCallsAnswOverCTI)
)+ SUM(AgentStatistics.DurCallsInternalReceived)
-(( SUM(AgentStatistics.DurCallsInternalReceived)
- SUM(AgentStatistics.DurCallsConstInternalRcvd)
- SUM(AgentStatistics.DurCallsTransInternalRcvd)
- SUM(AgentStatistics.DurCallsConfInternalRcvd)
)))/((( SUM(AgentStatistics.CallsAnswPriACD)+ SUM(AgentStatistics.CallsAnswOverACD)
+ SUM(AgentStatistics.CallsAnswPriCTI)
+ SUM(AgentStatistics.CallsAnswOverCTI)
)+ SUM(AgentStatistics.CallsInternalReceived)
-(( SUM(AgentStatistics.CallsInternalReceived)
- SUM(AgentStatistics.CallsConstInternalRcvd)
)))+(( SUM(AgentStatistics.CallsAnswPriACDStart_Range)
+ SUM(AgentStatistics.CallsAnswOverACDStart_Range)
+ SUM(AgentStatistics.CallsAnswPriCTIStart_Range)
+ SUM(AgentStatistics.CallsAnswOverCTIStart_Range)
)+ SUM(AgentStatistics.CallsInternalReceivedStart_Range)
-( SUM(AgentStatistics.CallsInternalReceivedStart_Range)
- SUM(AgentStatistics.CallsConstInternalRcvdStart_Range)
))))
FROM   (("CallCenter1"."dbo"."AgentStatistics" "AgentStatistics"
	INNER JOIN "CallCenter1"."dbo"."Agent" "Agent" ON "AgentStatistics"."AgentID"="Agent"."AgentId")
	INNER JOIN "CallCenter1"."dbo"."AgentMap" "AgentMap" ON "Agent"."AgentIdentity"="AgentMap"."AgentIdentity")
	INNER JOIN "CallCenter1"."dbo"."AgentFolder" "AgentFolder" ON "AgentMap"."AgentFolderIdentity"="AgentFolder"."AgentFolderIdentity"
	WHERE ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
			AND "AgentStatistics"."ReportDayDate" < GETDATE() )
		AND ("AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'}
			OR "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			OR ("AgentStatistics"."ReportDayDate" >= {ts '2008-09-17 00:00:00'}
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			AND "AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			OR ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			AND "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND "AgentMap"."AgentFolderIdentity" = 9
	GROUP BY "Agent"."AgentId"

There are 10 kinds of people: those who understand binary and those who don't

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 17:01:13

Here is an example of CASE and NULLIF methods people use to avoid that error. You can incorporate it in your expression - I'm not touching it


select case 
       when col2 = 0 then 0
       else col1 / col2
       end
from ...
--returns 0 instead of "devide by zero" error

OR

select col1 / nullIf(col2, 0)
from ...
--returns NULL instead of "devide by zero" error

Be One with the Optimizer
TG

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-22 : 17:40:15

Thanks everyone, I got it to work! I even got it to order the results as I wanted, the thing is that I only want the results whose AvgRate > 0 but I'm not sure if I can refer to that column in the WHERE clause. Any ideas of how I can achieve this?


SELECT TOP 5 "Agent"."AgentId", "Agent"."AgentName", ((( SUM(AgentStatistics.DurCallsAnswPriACD)
+ SUM(AgentStatistics.DurCallsAnswPriCTI)
+ SUM(AgentStatistics.DurCallsAnswOverACD)
+ SUM(AgentStatistics.DurCallsAnswOverCTI)
)+ SUM(AgentStatistics.DurCallsInternalReceived)
-(( SUM(AgentStatistics.DurCallsInternalReceived)
- SUM(AgentStatistics.DurCallsConstInternalRcvd)
- SUM(AgentStatistics.DurCallsTransInternalRcvd)
- SUM(AgentStatistics.DurCallsConfInternalRcvd)
)))/
(
CASE WHEN ((( SUM(AgentStatistics.CallsAnswPriACD)
+ SUM(AgentStatistics.CallsAnswOverACD)
+ SUM(AgentStatistics.CallsAnswPriCTI)
+ SUM(AgentStatistics.CallsAnswOverCTI)
)+ SUM(AgentStatistics.CallsInternalReceived)
-(( SUM(AgentStatistics.CallsInternalReceived)
- SUM(AgentStatistics.CallsConstInternalRcvd)
)))+(( SUM(AgentStatistics.CallsAnswPriACDStart_Range)
+ SUM(AgentStatistics.CallsAnswOverACDStart_Range)
+ SUM(AgentStatistics.CallsAnswPriCTIStart_Range)
+ SUM(AgentStatistics.CallsAnswOverCTIStart_Range)
)+ SUM(AgentStatistics.CallsInternalReceivedStart_Range)
-( SUM(AgentStatistics.CallsInternalReceivedStart_Range)
- SUM(AgentStatistics.CallsConstInternalRcvdStart_Range)
))) > 0 THEN ((( SUM(AgentStatistics.CallsAnswPriACD)
+ SUM(AgentStatistics.CallsAnswOverACD)
+ SUM(AgentStatistics.CallsAnswPriCTI)
+ SUM(AgentStatistics.CallsAnswOverCTI)
)+ SUM(AgentStatistics.CallsInternalReceived)
-(( SUM(AgentStatistics.CallsInternalReceived)
- SUM(AgentStatistics.CallsConstInternalRcvd)
)))+(( SUM(AgentStatistics.CallsAnswPriACDStart_Range)
+ SUM(AgentStatistics.CallsAnswOverACDStart_Range)
+ SUM(AgentStatistics.CallsAnswPriCTIStart_Range)
+ SUM(AgentStatistics.CallsAnswOverCTIStart_Range)
)+ SUM(AgentStatistics.CallsInternalReceivedStart_Range)
-( SUM(AgentStatistics.CallsInternalReceivedStart_Range)
- SUM(AgentStatistics.CallsConstInternalRcvdStart_Range)
)))
ELSE NULL
END
) 
) [AvgRate]
FROM   (("CallCenter1"."dbo"."AgentStatistics" "AgentStatistics"
	INNER JOIN "CallCenter1"."dbo"."Agent" "Agent" ON "AgentStatistics"."AgentID"="Agent"."AgentId")
	INNER JOIN "CallCenter1"."dbo"."AgentMap" "AgentMap" ON "Agent"."AgentIdentity"="AgentMap"."AgentIdentity")
	INNER JOIN "CallCenter1"."dbo"."AgentFolder" "AgentFolder" ON "AgentMap"."AgentFolderIdentity"="AgentFolder"."AgentFolderIdentity"
	WHERE ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
			AND "AgentStatistics"."ReportDayDate" < GETDATE() )
		AND ("AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'}
			OR "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= DATEADD(dd,-1,GETDATE())
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			OR ("AgentStatistics"."ReportDayDate" >= {ts '2008-09-17 00:00:00'}
				AND "AgentStatistics"."ReportDayDate" < DATEADD(dd,-1,GETDATE()) )
			AND "AgentStatistics"."ReportDayTime" >= {ts '1900-01-01 11:00:00'} )
		AND ( NOT ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			OR ("AgentStatistics"."ReportDayDate" >= GETDATE()
				AND "AgentStatistics"."ReportDayDate" < GETDATE() )
			AND "AgentStatistics"."ReportDayTime" < {ts '1900-01-01 11:00:00'} )
		AND "AgentMap"."AgentFolderIdentity" = 9
		--AND AvgRate > 0
	GROUP BY "Agent"."AgentId", "Agent"."AgentName"
	ORDER BY AvgRate

There are 10 kinds of people: those who understand binary and those who don't

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-22 : 18:37:34

Use :

Having Avgrate>0

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-23 : 09:49:55

quote:
Originally posted by sodeep

Use :

Having Avgrate>0

I replaced AND AvgRate > 0 with HAVING AvgRate > 0 and I get an error: Incorrect syntax near the keyword 'GROUP'
Am I doing something wrong?

There are 10 kinds of people: those who understand binary and those who don't

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-23 : 10:05:32

Leave the WHERE clause the way it was (prior to adding "AND AvgRate > 0").
Add the HAVING clause between the GROUP BY clause and the ORDER BY clause.

It wouldn't hurt to study the SELECT topic in Books Online - you should at least know the basic clauses and the order they need to appear

Be One with the Optimizer
TG

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-23 : 10:16:27

quote:
Originally posted by TG

Leave the WHERE clause the way it was (prior to adding "AND AvgRate > 0").
Add the HAVING clause between the GROUP BY clause and the ORDER BY clause.

It wouldn't hurt to study the SELECT topic in Books Online - you should at least know the basic clauses and the order they need to appear

Be One with the Optimizer
TG

(@TG Thanks for your help and suggestions.)

That still gives me an error: Invalid column name 'AvgRate'

There are 10 kinds of people: those who understand binary and those who don't

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-23 : 10:35:47

You're welcome :)

If AvgRate is a column alias then you will need to repeat the (AvgRate) exression in the HAVING clause rather than refer to the alias.

Be One with the Optimizer
TG

h4x0rmx
Starting Member

14 Posts

Posted - 2008-09-23 : 10:39:34

quote:
Originally posted by TG

You're welcome :)

If AvgRate is a column alias then you will need to repeat the (AvgRate) exression in the HAVING clause rather than refer to the alias.

Be One with the Optimizer
TG

Nice!!! Thanks again!

There are 10 kinds of people: those who understand binary and those who don't

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources