Help with simple GROUP BY query...

Hi,

I've got a table of data with 3 fields as follows:

SupplierID, ProductID, Price
1, 1, 1.50
2, 1, 2.50
3, 1, 3.50
1, 2, 1.20

etc.

Important point to note is that for each value of ProductID there are
multiple records.

What I want to do is the following:

1. Get a count of the number of unique ProductIDs for which there is
no SupplierIDs offering prices below some value. I thought this would
work, but it does not:

SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY
ProductID

I get "invalid use of group function".


2. Get a handle onto all of the products that match. I've been using
this:

SELECT *,MIN(Price) as MinPrice FROM Inventory WHERE MinPrice>'1.49'
GROUP BY ProductID

I get "invalid use of group function".


Thanks in advance,

Aetherweb <jeffsnox@gmail.com> wrote:

> Hi,
>
> I've got a table of data with 3 fields as follows:
>
> SupplierID, ProductID, Price
> 1, 1, 1.50
> 2, 1, 2.50
> 3, 1, 3.50
> 1, 2, 1.20
>
> etc.
>
> Important point to note is that for each value of ProductID there are
> multiple records.
>
> What I want to do is the following:
>
> 1. Get a count of the number of unique ProductIDs for which there is
> no SupplierIDs offering prices below some value. I thought this would
> work, but it does not:
>
> SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY
> ProductID
>
> I get "invalid use of group function".
>
>
> 2. Get a handle onto all of the products that match. I've been using
> this:
>
> SELECT *,MIN(Price) as MinPrice FROM Inventory WHERE MinPrice>'1.49'
> GROUP BY ProductID
>
> I get "invalid use of group function".


Does this query help you?

SELECT * FROM
(
SELECT x.SupplierID as 'supplier',
x.ProductID as 'product',
y.SupplierID as 'cheaper',
x.Price
FROM inventory x
LEFT JOIN Inventory y
ON y.ProductID = x.ProductID
AND x.Price > y.Price
) z
WHERE z.cheaper IS NULL;
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel

On Mar 8, 4:44 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> Aetherweb <jeffs...@gmail.com> wrote:
> > Hi,
>
> > I've got a table of data with 3 fields as follows:
>
> > SupplierID, ProductID, Price
> > 1, 1, 1.50
> > 2, 1, 2.50
> > 3, 1, 3.50
> > 1, 2, 1.20
>
> > etc.
>
> > Important point to note is that for each value of ProductID there are
> > multiple records.
>
> > What I want to do is the following:
>
> > 1. Get a count of the number of unique ProductIDs for which there is
> > no SupplierIDs offering prices below some value. I thought this would
> > work, but it does not:
>
> > SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY
> > ProductID
>
> > I get "invalid use of group function".
>
> > 2. Get a handle onto all of the products that match. I've been using
> > this:
>
> > SELECT *,MIN(Price) as MinPrice FROM Inventory WHERE MinPrice>'1.49'
> > GROUP BY ProductID
>
> > I get "invalid use of group function".
>
> Does this query help you?
>
> SELECT * FROM
> (
> SELECT x.SupplierID as 'supplier',
> x.ProductID as 'product',
> y.SupplierID as 'cheaper',
> x.Price
> FROM inventory x
> LEFT JOIN Inventory y
> ON y.ProductID = x.ProductID
> AND x.Price > y.Price
> ) z
> WHERE z.cheaper IS NULL;
> --
> Rik Wasmus
> Posted on Usenet, not any forum you might see this in.
> Ask Smart Questions:http://tinyurl.com/anel

That does help - makes it clear that my simple problem isn't as simple
as it first appears. Problem is that I need this to crunch quite a
large amount of data very quickly, and it runs quite often. I think
I'll have to create a lookup table and query that instead.

Thanks for your help.

Aetherweb <jeffsnox@gmail.com> wrote:
>> > What I want to do is the following:
>>
>> > 1. Get a count of the number of unique ProductIDs for which there is
>> > no SupplierIDs offering prices below some value. I thought this would
>> > work, but it does not:
>>
>> > SELECT COUNT(*) FROM Inventory WHERE MIN(Price)>'1.49' GROUP BY
>> > ProductID
>>
>> > I get "invalid use of group function".
>>
>> > 2. Get a handle onto all of the products that match. I've been using
>> > this:
>>
>> > SELECT *,MIN(Price) as MinPrice FROM Inventory WHERE MinPrice>'1.49'
>> > GROUP BY ProductID
>>
>> > I get "invalid use of group function".
>>
>> Does this query help you?
>>
>> SELECT * FROM
>> (
>> SELECT x.SupplierID as 'supplier',
>> x.ProductID as 'product',
>> y.SupplierID as 'cheaper',
>> x.Price
>> FROM inventory x
>> LEFT JOIN Inventory y
>> ON y.ProductID = x.ProductID
>> AND x.Price > y.Price
>> ) z
>> WHERE z.cheaper IS NULL;
>> --
>> Rik Wasmus
>> Posted on Usenet, not any forum you might see this in.
>> Ask Smart Questions:http://tinyurl.com/anel
>
> That does help - makes it clear that my simple problem isn't as simple
> as it first appears. Problem is that I need this to crunch quite a
> large amount of data very quickly, and it runs quite often. I think
> I'll have to create a lookup table and query that instead.

Don not assume that based on my response. Lack of knowledge often makes my
queries (seem) more complex then need be, there might be quicker solutions
out there.
--
Rik Wasmus
Posted on Usenet, not any forum you might see this in.
Ask Smart Questions: http://tinyurl.com/anel