Order By Problems - 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)

Order By Problems

Author

Topic

mswanson48
Starting Member

2 Posts

Posted - 2012-04-19 : 16:28:31

In the example below, I'm trying to select records 51-70 in the result set. I have this working as desired until I try to sort on a field other than locationName. Once I use another field, I get an error message similar to "Column "dbo.callMetrics.ClosedCallWty" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

If I added the column to the GROUP BY clause I can get past this message, but the results are no longer combined based on location name.

I've also been able to keep the combined, but end up with "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

I can get past any of these error messages, but I don't seem to be able to keep the results consolidated and sorted at the same time.

Please help!



SELECT     TOP (20) dbo.location.locationID, dbo.location.locationName, SUM(DISTINCT dbo.callMetrics.ClosedCallWty) AS closedCallWty,
	SUM(DISTINCT dbo.callMetrics.ClosedCallTM) AS closedCallTM, SUM(DISTINCT dbo.callMetrics.ClosedCallMA) AS closedCallMA,
	SUM(DISTINCT dbo.callMetrics.CallsCompleted) AS callsCompleted, AVG(DISTINCT dbo.callMetrics.HoldCalls) AS holdCalls

FROM         dbo.callMetrics INNER JOIN
	dbo.location ON dbo.callMetrics.RepBranch = dbo.location.locationCode INNER JOIN
	dbo.locationRelationship ON dbo.location.locationID = dbo.locationRelationship.locationID

WHERE     (dbo.callMetrics.ReportDate >= CONVERT(DATETIME, '02/01/2011 00:00:00', 102)) AND 
	(dbo.callMetrics.ReportDate <= CONVERT(DATETIME, '02/28/2011 23:59:59', 102)) AND dbo.location.locationID NOT IN (

		SELECT     TOP (50) dbo.location.locationID
		
		FROM         dbo.callMetrics INNER JOIN
			dbo.location ON dbo.callMetrics.RepBranch = dbo.location.locationCode INNER JOIN
			dbo.locationRelationship ON dbo.location.locationID = dbo.locationRelationship.locationID
		
		WHERE     (dbo.callMetrics.ReportDate >= CONVERT(DATETIME, '02/01/2011 00:00:00', 102)) AND 
			(dbo.callMetrics.ReportDate <= CONVERT(DATETIME, '02/28/2011 23:59:59', 102))
		
		GROUP BY dbo.location.locationName, dbo.location.locationID, dbo.locationRelationship.isAllowed, dbo.locationRelationship.kpiUserID

		HAVING      (dbo.locationRelationship.isAllowed = 1) AND (dbo.locationRelationship.kpiUserID = 283)

		ORDER BY closedCallWty asc)

GROUP BY dbo.location.locationName, dbo.location.locationID, dbo.locationRelationship.isAllowed, dbo.locationRelationship.kpiUserID

HAVING      (dbo.locationRelationship.isAllowed = 1) AND (dbo.locationRelationship.kpiUserID = 283)

ORDER BY closedCallWty asc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 16:34:41

thats obvious. Unless you group on that field you cant use it directly in order by, as grouping causes rows to be aggregated andmight involve multiple value of closedCallWty within same group so order by on that doesnt make sense

perhaps you can explain what you're trying to do with some sample data

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

mswanson48
Starting Member

2 Posts

Posted - 2012-04-20 : 09:20:06

quote:
Originally posted by visakh16

thats obvious. Unless you group on that field you cant use it directly in order by, as grouping causes rows to be aggregated andmight involve multiple value of closedCallWty within same group so order by on that doesnt make sense

perhaps you can explain what you're trying to do with some sample data

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

Here is a small sampling of the raw data (the query actually pulls from a view which pulls in some labels for abbreviations as well as does a permissions check based on permissions that are defined in another table):


5000,APL,APLGROSV,4/1/2011,0,0,0,0,0,0,0,0,0,0,0
6000,BJI,CADMIN,4/1/2011,0,0,0,0,0,0,0,0,0,0,0
5000,APL,APLGROSV,4/2/2011,0,0,0,0,0,0,0,0,0,0,0
6000,BJI,CADMIN,4/2/2011,0,0,0,0,0,0,0,0,0,0,0
5000,APL,APLGROSV,4/3/2011,0,0,0,0,0,0,0,0,0,0,0
6000,BJI,CADMIN,4/3/2011,0,0,0,0,0,0,0,0,0,0,0

I would like a single row returned for 5000 and a single row returned for 6000 (among the several hundred other options) that combine all of the totals if the date is between the beginning and ending specified. That piece of it is working as desired.

What I also would like to be able to do is then sort the data for each of the columns. Currently, I can only sort based on the location (which I understand since that is all that is included in the group by). The thought of sorting by the other columns is easy to wrap my mind around - the technical implementation of it isn't so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 15:20:56

as told before if you want to sort on any columns which is not part of GROUP BY then you've to apply aggregation function like MIN(),MAX() over them and then use it in sorting
I dont know why you want to do like that though as once grouped the other columns will not have any unique existence.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources