Re: Unique Values Query
- From: "Ruskin Hardie" <feckoff@xxxxxxxxxx>
- Date: Wed, 7 Dec 2005 09:37:32 +1300
Single Query Solution;
SELECT Sales.Customer,
Sales.Item,
Sales.Price,
Sum(Sales.Quantity) AS TotalQuantity,
Sum([Price]*[Quantity]) AS TotalValue
FROM Sales
WHERE EXISTS (
SELECT ASales.Customer
FROM Sales AS ASales
WHERE ASales.Customer = Sales.Customer
AND ASales.Item = Sales.Item
AND ASales.Price <> Sales.Price
)
GROUP BY Sales.Customer, Sales.Item, Sales.Price;
"Don" <Don@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1C45F81E-20CA-4FF8-80F4-9A7B70581F62@xxxxxxxxxxxxxxxx
> I have a table with about 125,000 records. The fields consist of Customer
> Number, Item Number and Price. Each record indicates a purchase by a
> customer. Many of the purchases are for the same item, at the same price;
> however, there are occasional price increases. Here is what I want to do.
I
> want to return every instance of customer purchases when the price is
> different. For instance, if customer 1 bought item 2 for $3.00 a total of
10
> times and then the price went to $4.00. I want the query to return 1
record
> of customer 1 buying item 2 for $3.00 and 1 record of customer 1 buying
item
> 2 for $4.00 (This I can do by selecting Unique Values in the query's
> properties. Here's the kicker - I don't want the query to return any
items
> that have only had 1 price (For instance, 1 unique value) What I'm trying
to
> do is identify every customer that have had price changes and what they
have
> been. Any help would be useful if I haven't confused you. Thanks...
> --
> Don Rountree
.
- Prev by Date: Re: Unique Values Query
- Next by Date: Re: Show Month and Year only
- Previous by thread: Re: Unique Values Query
- Next by thread: Re: Show Month and Year only
- Index(es):
Relevant Pages
|
