RE: Another SQL Question

[Alan Shore <AlanShore@xxxxxxxx>]

Chris - as the Guiness commercial goes -------- BRILLIANT
It works like a charm.
Much appreciated


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


midrange-l-bounces@xxxxxxxxxxxx wrote on 12/04/2006 04:15:21 PM:

> You can wrap your statement in a CTE, which lets you treat the results
> of a query like a regular table.
>
> With Prices_and_counts as(
> SELECT
>  SKUNO,
>  DIVISION,
>  sum(PRICE) as total_price,
>  avg(price) as average_price,
>  count(*) as count_of_things
> FROM
>  FileA
> GROUP BY
>  SKUNO,
>  DIVISION
> )
>
> Select
>  SKUNO,
>  DIVISION,
>  total_price,
>  average_price,
>  count_of_things
> from
>  prices_and_counts
> order by
>  count_of_things desc,
>  SKUNO,
>  DIVISION
>
> Chris
>
> --
> 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.