By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,856 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,856 IT Pros & Developers. It's quick & easy.

Need help with aggregate function query

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
"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

P: n/a
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.