Need help with aggregate function query

[Jim]

Need help with aggregate function...for each unique product, I need the
provider with the cheapest cost factor

Here't the table (Table1)

ID product provider factor
1 123456 abc .050
2 123456 def .035
3 666666 def .040
4 123456 ghi .080
5 666666 abc .026
6 666666 ghi .054

"Logical" query is

SELECT [Table1].[product], Min([Table1].[factor]) AS cheapest,
[Table1].[provider]
FROM Table1
GROUP BY [Table1].[product];

Problem is adding the provider - error
"You tried to execute a query that does not include the specified
expression 'provider' as part of an aggregate function"

Results wanted:

product cheapest provider
123456 .035 def
666666 .026 abc

Thanks in Advance,

Jim

[Jonathan Amend]

Don't you just need to add provider to the GROUP BY list?

SELECT [Table1].[product], Min([Table1].[factor]) AS cheapest,
[Table1].[provider]
FROM Table1
GROUP BY [Table1].[product], [Table1].[provider];

Also try using Access's query builder; It will do this automatically for
you.

"Jim" <ji*@txharts.co m> wrote in message
news:wt******** *******@newssvr 24.news.prodigy .com...

Need help with aggregate function...for each unique product, I need the
provider with the cheapest cost factor

Here't the table (Table1)

ID product provider factor
1 123456 abc .050
2 123456 def .035
3 666666 def .040
4 123456 ghi .080
5 666666 abc .026
6 666666 ghi .054

"Logical" query is

SELECT [Table1].[product], Min([Table1].[factor]) AS cheapest,
[Table1].[provider]
FROM Table1
GROUP BY [Table1].[product];

Problem is adding the provider - error
"You tried to execute a query that does not include the specified
expression 'provider' as part of an aggregate function"

Results wanted:

product cheapest provider
123456 .035 def
666666 .026 abc

Thanks in Advance,

Jim

[John Winterbottom]

"Jim" <ji*@txharts.co m> wrote in message
news:wt******** *******@newssvr 24.news.prodigy .com...

Need help with aggregate function...for each unique product, I need the
provider with the cheapest cost factor

Here't the table (Table1)

ID product provider factor
1 123456 abc .050
2 123456 def .035
3 666666 def .040
4 123456 ghi .080
5 666666 abc .026
6 666666 ghi .054

"Logical" query is

SELECT [Table1].[product], Min([Table1].[factor]) AS cheapest,
[Table1].[provider]
FROM Table1
GROUP BY [Table1].[product];

when you use the group by clause, each item in the select list must be an
aggregate, (i.e. max, min etc.), or it must appear in the group by list. Try

select product, Min(factor) as cheapest,
provider
from Table1
group by product, provider

[Jim]

Still gives 6 results and wanting...

product cheapest provider
123456 .035 def
666666 .026 abc

Jim

when you use the group by clause, each item in the select list must be an
aggregate, (i.e. max, min etc.), or it must appear in the group by list. Try
select product, Min(factor) as cheapest,
provider
from Table1
group by product, provider

[John Winterbottom]

"Jim" <ji*@txharts.co m> wrote in message
news:gh******** *********@newss vr23.news.prodi gy.com...

Still gives 6 results and wanting...

product cheapest provider
123456 .035 def
666666 .026 abc

sorry didn't read your post preoperly. There are several methods of solving
this type of problem at http://www.mvps.org/access/queries/qry0020.htm

If factor is a money data type, (not double), you could do this:

select a.product, a.cheapest, t.provider
from
(
select product, min(factor) as cheapest
from Table1
group by product
) as a
inner join Table1 as t
on a.provider = t.provider
and a.cheapest = t.factor

If factor is a double data type it's not a good idea to join on it. Try
something like this

select t.product, min(factor) as cheapest,
(
select top 1 provider from Table1 t2
where t2.product = t.product
order by factor desc
) as provider
from Table1 as t
group by t.product
The problem with the above query is that it relies on the proprietary top
clause. If that's not a problem for you then this might be the way to go.

[Jim]

Thanks!! Got it!! - I'll check out the link as well - Jim

"John Winterbottom" <as******@hotma il.com> wrote in message
news:2k******** *****@uni-berlin.de...

"Jim" <ji*@txharts.co m> wrote in message
news:gh******** *********@newss vr23.news.prodi gy.com...

Still gives 6 results and wanting...

product cheapest provider
123456 .035 def
666666 .026 abc

sorry didn't read your post preoperly. There are several methods of

solving this type of problem at http://www.mvps.org/access/queries/qry0020.htm

If factor is a money data type, (not double), you could do this:

select a.product, a.cheapest, t.provider
from
(
select product, min(factor) as cheapest
from Table1
group by product
) as a
inner join Table1 as t
on a.provider = t.provider
and a.cheapest = t.factor

If factor is a double data type it's not a good idea to join on it. Try
something like this

select t.product, min(factor) as cheapest,
(
select top 1 provider from Table1 t2
where t2.product = t.product
order by factor desc
) as provider
from Table1 as t
group by t.product
The problem with the above query is that it relies on the proprietary top
clause. If that's not a problem for you then this might be the way to go.