473,725 Members | 1,987 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 6060
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

Nov 13 '05 #2
"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
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.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.


Nov 13 '05 #5
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.


Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3435
by: Toby Dann | last post by:
I have an aggregate query as recordsource for a form to show a list of invoices, including the totals (calculated by the aggregate query - I'm trying to duplicate as little info as possible here). What I want to achieve is a recordcount (of the number of records returned by the aggregate query) and Sum of the Invoice totals, but Access does not seem to like calculating either of these on an aggregate query. Any ideas? Am I going about...
10
11933
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to post) *I'm running a total query, of the form
7
1446
by: John Ortt | last post by:
Hi there, I am trying to do a two level embeded query but it is causing Dr Watson errors. The line which is causing the problem is: CFCDeci: (Select sum (FlexChangeDeci) FROM as WHERE (. <= .) and (. >= .);)
12
25293
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id, GCP.Name FROM Gov_Capital_Project GCP,
1
3159
by: Scott Gerhardt | last post by:
Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum() values for each distinct wid as in the example below, but except for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). Also, performance wise, would it be better to build a function for this query. The table has 9 million records and these...
0
1588
by: BillCo | last post by:
just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
5
6097
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
3
12464
by: ncsthbell | last post by:
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I hope someone can be so kind to guide me!!! I have 2 tables: Table1 - columns: Period FuelType Table2 - columns: Period
7
2480
by: kpfunf | last post by:
Getting the following error opening a report, cannot figure out the cause: "You tried to execute a query that does not include the specified expression 'RQ_FuelQuoteReportHistory.Vendor' as part of an aggregate function" Not sure why this comes up as Vendor is not part of any aggregate function (that I know of!). The query runs fine. The error occurs when opening the report. Vendor is not used in sorting or grouping. I tried deleting the...
0
8886
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8750
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9401
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9104
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8090
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6701
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6010
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4780
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2154
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.