Having and Group By clause

Question

I came across this question yesterday. I am looking for an explanation for just one point.

Which of the following statements are TRUE about an SQL query?

P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause

Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause

R : All attributes used in the GROUP BY clause must appear in the SELECT clause

S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

(A) P and R.
(B) P and S.
(C) Q and R.
(D) Q and S.

I selected A as an answer. But actually B is the correct answer.

I want to know that some websites mention R is correct, whereas others say S is correct. Which statement is correct between the last two?

Answer 1

"R" is incorrect because that is saying every field being grouped on also needs to be selected which is definitely not true. The fields in a GROUP BY clause are what define how to collapse a set of rows related by those fields. But there's no reason you have to return those same fields in the SELECT list. If you wanted to aggregate on a non-grouped field, you're not required to also return the grouped fields. For example the following is a valid query:

SELECT SUM(Price) AS TotalAmountSold
FROM SalesOrders
GROUP BY ProductName

Of course, there's not much meaning in the results of that query because it'll just return a list of total amounts without the actual ProductName associated with them. So there's no telling which total goes to which product.

But the fields in a GROUP BY clause may be added to the SELECT list (without any aggregation function) like so:

SELECT ProductName, SUM(Price) AS TotalAmountSold
FROM SalesOrders
GROUP BY ProductName

This makes for a meaningful query. But in summary the reason why "R" is incorrect is because it says "must" which isn't true, rather they may appear in the SELECT list.