Thanks. Why didn't I think of that?

--
Ray Briggs II
Lone Star Medical Products, Inc.
xxx-xxx-xxxx  Ext. 148
mailto:ray@l...


"Jeff Mason" <je.mason@a...> wrote in message
news:230792@s..._language...
>
> How about:
>
> SELECT Product, SUM(QTY), SUM(QTY*Price)
> FROM Invoice
> GROUP BY Product;
>
> --
> Jeff Mason              Custom Apps, Inc.
> Jeff@c...
>
>
> -----Original Message-----
> From: Ray [mailto:ray@l...]
> Sent: Thursday, October 31, 2002 4:39 PM
> To: sql language
> Subject: [sql_language] Fun With Aggregates
>
>
> Here is my initial table.
> Product     QTY   Price
> 3311-8G   12    10.00
> 3307G       12    10.00
> 3111-8G     4     20.00
> 3307G2      3     10.00
> 3311-8G    3      20.00
>
> Its Product, qty of product and price per product. I want to get a summary
> of the products for this table where each product is only listed once and
> the total number bought as well as the total spent is listed with it.
> Basically this
>
> 3311-8G    19    260.00
> 3307G2       3    30.00
> 3307G       12    120.00
>
> With the following SQL statement:
> select invproduct,sum(invqty) ,sum(invqty)*invprice  from invoice group by
> invproduct,invprice
> I get
> 3307G     12     120.00
> 3307G2    3     30.00
> 3311-8G   7      140.00
> 3311-8G  12     120.00
>
> As you can see 3311-8G has two lines and I only want one. I know it is
doing
> this because I am grouping by price but I don't see anyway to avoid it
since
> I need price in the SELECT.
>
> Any ideas on how I can do this in one SQL statement.
>
> Ray
>
>
>
>
>
>