Sorting on Aggregate Functions

[TheOneRing]

Gedday,

I have the following SQL Statement:

SELECT tblCustomers.CompanyName AS Company, tblCustomers.CustomerID AS CustomerID, tblCustomers.TypeofCustomer, Count(*) AS Numcalls, Sum(tblInvoice.dblSubTotal) AS TotalDollars
FROM tblCustomers INNER JOIN tblInvoice ON (tblCustomers.CustomerID = tblInvoice.strShipTo) AND (tblCustomers.CustomerID = tblInvoice.strBillTo)
WHERE (((tblInvoice.dtmDate) Between CDate('01-03-02') And CDate('31-03-02')) AND ((tblInvoice.strType)<>'PB'))
GROUP BY tblCustomers.CompanyName, tblCustomers.TypeofCustomer,tblCustomers.CustomerID

now this works fine and returns the information that I want, but I want to be able to sort the records by the number of calls (NumCalls) or by the total dollars (TotalDollars) but when I put in ORDER BY Numcalls or ORDER BY TotalDollars, it doesn't work.

The only way I can think of doing it is by selecting the records into a temp table and then selecting them again and then sorting them.

Does anyone have any Ideas please.

 

[fluteplr]

You can't use the names, you have to use the functions again.

As in,

order by Count(*)

or

order by Sum(tblInvoice.dblSubTotal)

 

[tlbroadbent]

Here are two options.

Example: Use aggregate in Order By clause[ul]SELECT
tblCustomers.CompanyName AS Company,
tblCustomers.CustomerID AS CustomerID,
tblCustomers.TypeofCustomer,
Count(*) AS Numcalls,
Sum(tblInvoice.dblSubTotal) AS TotalDollars
FROM tblCustomers
INNER JOIN tblInvoice
ON tblCustomers.CustomerID = tblInvoice.strShipTo
AND tblCustomers.CustomerID = tblInvoice.strBillTo
WHERE tblInvoice.dtmDate
Between CDate('01-03-02')
And CDate('31-03-02')
AND tblInvoice.strType<>'PB'
GROUP BY
tblCustomers.CompanyName,
tblCustomers.TypeofCustomer,
tblCustomers.CustomerID
Order By count(*) OR
Order By Sum(tblInvoice.dblSubTotal)[/ul]Example: Select from a sub query and order by column name[ul]Select * From
(SELECT
tblCustomers.CompanyName AS Company,
tblCustomers.CustomerID AS CustomerID,
tblCustomers.TypeofCustomer,
Count(*) AS Numcalls,
Sum(tblInvoice.dblSubTotal) AS TotalDollars
FROM tblCustomers
INNER JOIN tblInvoice
ON tblCustomers.CustomerID = tblInvoice.strShipTo
AND tblCustomers.CustomerID = tblInvoice.strBillTo
WHERE tblInvoice.dtmDate
Between CDate('01-03-02')
And CDate('31-03-02')
AND tblInvoice.strType<>'PB'
GROUP BY
tblCustomers.CompanyName,
tblCustomers.TypeofCustomer,
tblCustomers.CustomerID) as Qry
Order By NumCalls OR
Order By TotalDollars[/ul] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[fluteplr]

The second one never occured to me although it is sort of obvious now that you mention it. Do you know if there is a performance hit?

 

[tlbroadbent]

I've never compared performance. Maybe someday but it is late and I'm done for today. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.