469,299 Members | 2,079 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,299 developers. It's quick & easy.

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
Nov 13 '05 #1
5 5860
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.com> wrote in message
news:wt***************@newssvr24.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

Nov 13 '05 #2
"Jim" <ji*@txharts.com> wrote in message
news:wt***************@newssvr24.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
Nov 13 '05 #3
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

Nov 13 '05 #4
"Jim" <ji*@txharts.com> wrote in message
news:gh*****************@newssvr23.news.prodigy.co m...
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.


Nov 13 '05 #5
Jim
Thanks!! Got it!! - I'll check out the link as well - Jim

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2k*************@uni-berlin.de...
"Jim" <ji*@txharts.com> wrote in message
news:gh*****************@newssvr23.news.prodigy.co m...
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.


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Toby Dann | last post: by
7 posts views Thread by John Ortt | last post: by
1 post views Thread by Scott Gerhardt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.