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

Help me understand this sql code

P: n/a
I have this bit of code:
SELECT itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;

it lists the minimum price for each itemcode group. It works fine.

But when I want to see the last name of the person who bought this item, it
give me that aggregate error saying the lastname is not part of the
aggregate function.
BUt that is silly, because if I put it in the group by, then the function
does not work.

SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;
<< this gives an error

SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode, lastname;
this will not group by the itemcode only which I need, it groups but

itemcode and lastname

what do I do?
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
If you think about it logically, how could it give you the last name
information? You're telling it to group by the item code and give you the
minimum price of each. Fine. Say there are three prices for a particular
item code. The result is one line with the item code and the minimum price
of the three. Fine. Now you want the last name. Which last name does it give
you? There are three of them. How can it know which one you want?

You may say, "I want the one that's associated with the minimum price." OK;
but what if there are two last names that have the same price and both are
the minimum price. Which last name would it use?

Consider the following:

ItemCode: 1
LastName: Smith
Price: 100.00

ItemCode:1
LastName: Jones
Price: 100.00

ItemCode:1
LastName: Peters
Price: 200

In your current query, the line for item code 1 will read as follows:

ItemCode MinPrice
1 100.00

Now you want to add the last name. Which one does it use, Smith or Jones?
Hence the error.

The answer is either to group by the last name (which will ruin your item
code group by, as you found out), or create two queries -- one to get the
minimum price, and the other to get the name (in SQL Server you can do this
with one query, but in Access you'd need to use a stored subquery).

So, you would keep your first query the way it is. Call it Query1. Then
create a new query with the following SQL:

SELECT itemlist.ItemCode, itemlist.Price, First(itemlist.Name) AS Name
FROM itemlist INNER JOIN Query1 ON (itemlist.Price = Query1.MinPrice) AND
(itemlist.ItemCode = Query1.ItemCode)
GROUP BY itemlist.ItemCode, itemlist.Price

That would give you the first name in the case that there is more than one
with the same minimum price.

Neil
"Danny" <da********@hotmail.com> wrote in message
news:N_**********************@news4.srv.hcvlny.cv. net...
I have this bit of code:
SELECT itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;

it lists the minimum price for each itemcode group. It works fine.

But when I want to see the last name of the person who bought this item,
it
give me that aggregate error saying the lastname is not part of the
aggregate function.
BUt that is silly, because if I put it in the group by, then the function
does not work.

SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;
<< this gives an error

SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode, lastname;
this will not group by the itemcode only which I need, it groups but

itemcode and lastname

what do I do?

Nov 13 '05 #2

P: n/a
My question is similar except it involves the count aggregate:

I am counting the number of times a certain "code" is used. I want to
return the count, the code
and also the codes description. However each code has multiple
descriptions. Ideally I would like to return the most popular
description, but another option would be to return all of the
descriptions.

This is my code so far:

SELECT code, Count(code) as a
FROM codebank
GROUP BY code
ORDER BY a Desc

This works fine to return the count as well as the code, but attaching
the description to the output is giving me fits.

I've tried:

SELECT code, Count(code) as a, description
FROM codebank
GROUP BY code
ORDER BY a Desc

But I get the Error: "description is not included in an AGGREGATE
function or Group By. I understand that since I am using the count
aggregate it wants me to include the description inside of the GROUP
BY. However, this defeats my desired outcome. I get an output that
looks like this

# Code desc
-------------------------------------------------------------------
3 001 dollars
2 001 euros
2 002 laundry
1 003 food

What I want is:
# Code desc
-------------------------------------------------------------------
5 001 dollars (or dollars, euros)
2 002 laundry
1 003 food
Please Help....

Nov 13 '05 #3

P: n/a
Try adding all the select criteria into the group by statement

SELECT code, Count(code) as a, description
FROM codebank
GROUP BY code,a,description
ORDER BY a Desc
<ma*********@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
My question is similar except it involves the count aggregate:

I am counting the number of times a certain "code" is used. I want to
return the count, the code
and also the codes description. However each code has multiple
descriptions. Ideally I would like to return the most popular
description, but another option would be to return all of the
descriptions.

This is my code so far:

SELECT code, Count(code) as a
FROM codebank
GROUP BY code
ORDER BY a Desc

This works fine to return the count as well as the code, but attaching
the description to the output is giving me fits.

I've tried:

SELECT code, Count(code) as a, description
FROM codebank
GROUP BY code
ORDER BY a Desc

But I get the Error: "description is not included in an AGGREGATE
function or Group By. I understand that since I am using the count
aggregate it wants me to include the description inside of the GROUP
BY. However, this defeats my desired outcome. I get an output that
looks like this

# Code desc
-------------------------------------------------------------------
3 001 dollars
2 001 euros
2 002 laundry
1 003 food

What I want is:
# Code desc
-------------------------------------------------------------------
5 001 dollars (or dollars, euros)
2 002 laundry
1 003 food
Please Help....

Nov 13 '05 #4

P: n/a
Thanks, but I tried that and it gave me that first output I showed. I
only want to sort by the code.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.