Re: Another SQL Question

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Alan,

Other solutions have been given, here is another one.
You can also rename the column in the SELECT-clause and use that name in
the ORDER BY-clause:

SELECT SKUNO, DIVISION, sum(PRICE) AS totalPrice , avg(price) AS AvgPrice,
count(*) AS NoRecs
FROM FileA 
GROUP BY SKUNO, DIVISION 
ORDER BY totalPrice desc, SKUNO, DIVISION

Regards,
Carel Teijgeler


*********** REPLY SEPARATOR  ***********

On 4-12-2006 at 16:06 Alan Shore wrote:

> Hi everyone.
> I have just tried the following SQL
>
> SELECT SKUNO, DIVISION, sum(PRICE), avg(price), count(*) FROM
> FileA GROUP BY SKUNO, DIVISION ORDER BY sum(PRICE) desc, SKUNO,
> DIVISION
>
> with no problems
>
> However, when I try the following:-
> SELECT SKUNO, DIVISION, sum(PRICE), avg(price), count(*) FROM
> FileA GROUP BY SKUNO, DIVISION ORDER BY count(*) desc, SKUNO,
> DIVISION
>
> I get the error
>
> Use of function COUNT not valid.
>
> anybody have any thoughts how I can easily sort the results in descending
> count number order?
>
> Thanks in advance
>
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.