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]

Re: Need help with aggregate function query

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" <jim@txharts.co m> wrote in message
news:wtkDc.8960 $u64.11@newssvr 24.news.prodigy .com...[color=blue]
> 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
>
>[/color]

[John Winterbottom]

Re: Need help with aggregate function query

"Jim" <jim@txharts.co m> wrote in message
news:wtkDc.8960 $u64.11@newssvr 24.news.prodigy .com...[color=blue]
> 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];
>[/color]


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]

Re: Need help with aggregate function query

Still gives 6 results and wanting...

product cheapest provider
123456 .035 def
666666 .026 abc

Jim[color=blue]
>
>
> 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.[/color]
Try[color=blue]
>
> select product, Min(factor) as cheapest,
> provider
> from Table1
> group by product, provider
>
>[/color]

[John Winterbottom]

Re: Need help with aggregate function query

"Jim" <jim@txharts.co m> wrote in message
news:ghoDc.4107 $pF2.1236@newss vr23.news.prodi gy.com...[color=blue]
> Still gives 6 results and wanting...
>
> product cheapest provider
> 123456 .035 def
> 666666 .026 abc
>[/color]

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]

Re: Need help with aggregate function query

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

"John Winterbottom" <assaynet@hotma il.com> wrote in message
news:2k6nqbF184 kndU1@uni-berlin.de...[color=blue]
> "Jim" <jim@txharts.co m> wrote in message
> news:ghoDc.4107 $pF2.1236@newss vr23.news.prodi gy.com...[color=green]
> > Still gives 6 results and wanting...
> >
> > product cheapest provider
> > 123456 .035 def
> > 666666 .026 abc
> >[/color]
>
> sorry didn't read your post preoperly. There are several methods of[/color]
solving[color=blue]
> 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.
>
>
>
>
>
>[/color]