SQL grouping with "Invalid use of group" error

Question

I'll be upfront, this is a homework question, but I've been stuck on this one for hours and I just was looking for a push in the right direction. First I'll give you the relations and the hw question for background, then I'll explain my question:

Branch (BookCode, BranchNum, OnHand)

HW problem: List the BranchNum for all branches that have at least one book that has at least 10 copies on hand.

My question: I understand that I must take the SUM(OnHand) grouped by BookCode, but how do I then take that and group it by BranchNum? This is logically what I come up with and various versions:

select distinct BranchNum
from Inventory
where sum(OnHand) >= 10
group by BookCode;

but I keep getting an error that says "Invalid use of group function."

Could someone please explain what is wrong here?

UPDATE: I understand now, I had to use the HAVING statement, the basic form is this:

select distinct (what you want to display)
from (table)
group by 
having

I am guessing you need to use `HAVING sum(OnHand) >= 10` than `WHERE clause` , see [here](http://stackoverflow.com/questions/2330840/mysql-invalid-use-of-group-function) — Drixson Oseña, Oct 16 '13 at 03:05
You should group all `non aggregated` columns, i.e., all columns not summed, counted, etc. — Rubens Farias, Oct 16 '13 at 03:05
Also, use GROUP BY or DISTINCT (DISTINCT when you don't need aggregate functions), but not both. — Doug_Ivison, Oct 16 '13 at 04:15

score 0 · Answer 1 · answered Oct 16 '13 at 05:46

0

Try this one.

SELECT BranchNum 
FROM Inventory 
GROUP BY BranchNum 
HAVING SUM(OnHand) >= 10

You can also find Group By Clause with example here.

answered Oct 16 '13 at 05:46

Aditya

2,299
5
32
54

score 0 · Answer 2 · answered Oct 16 '13 at 05:48

Although all comments in the question seem to be valid and add information they all seem to be missing why your query is not working. The reason is simple and is strictly related by the state/phase at which the sum is calculated.

The where clause is the first thing that will get executed. This means it will filter all rows at the beginning. Then the group by will come in effect and will merge all rows that are not specified in the clause and apply the aggregated functions (if any).

So if you try to add an aggregated function to the where clause you're trying to aggregate before data is being grouped by and even filtered. The having clause gets executed after the group by and allows you to filter the aggregated functions, as they have already been calculated.

That's why you can write HAVING SUM(OnHand) >= 10 and you can't write WHERE SUM(OnHand) >= 10.

Hope this helps!

SQL grouping with "Invalid use of group" error

2 Answers2