Please consider offering answers and suggestions to help other students!
And if you fix a problem by following a suggestion here,
it would be great if other interested students could see a short
"Great, fixed it!" followup message.
Hello,
While I was preparing for the mid-term exam, in one of the slides I found the statement:
If SELECT list includes an aggregate function and there is no GROUP BY clause, SELECT list cannot reference a column out with an aggregate function. For example, the following is illegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
However, in the subsequent slides, I see these statements as examples of using the aggregate functions (couple of examples below):
SELECT COUNT(*) AS myCount
FROM PropertyForRent
WHERE rent > 350;
SELECT COUNT(staffNo) AS myCount,
SUM(salary) AS mySum
FROM Staff
WHERE position = ‘Manager’;
What I mean to say is that according to the first statement, aren't these examples illegal as well? They don't have the HAVING clause, but WHERE clauses instead.
Thanks in advance!
WHERE is used to filter data before aggregation
HAVING is used to filter data after aggregation (after GROUP BY clause)
As far as I remembered, you can actually use both WHERE and HAVING at the same time. Just note that WHERE will filter the data before the aggregation.
SELECT staffNo, COUNT(salary) FROM Staff;
This is wrong because you have regular column (staffNo) and then COUNT(salary) which is an aggregation function and the query have no GROUP BY clause for the regular column (staffNo)
SELECT COUNT(*) AS myCount FROM PropertyForRent WHERE rent > 350;
This is correct, you are doing WHERE to filter the data before aggregating it. And then you only select COUNT(*) which is an aggregation function and no regular column.
SELECT COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM Staff WHERE position = ‘Manager’;
This is also correct, because you are only selecting COUNT(staffNo) and SUM(salary) which are both aggregation function. See there are no regular column involved.
SELECT staffName, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
WHERE position = 'Manager';
This one however is incorrect because you have staffName column (regular column) selected and 2 aggregation.
To correct it simply do GROUP BY clause to staffName
SELECT staffName, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
WHERE position = 'Manager' GROUP BY staffName;
But if I am not mistaken you are allowed to do this
SELECT position, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM Staff
WHERE position = 'Manager';
Because you are doing filtering to the position column, then all the rows that remain after the WHERE clause filtering have the same position, so there won't be any need for a GROUP BY clause.
But I'm not really sure about this so it's better if you give it a try and maybe learn something new!
