Welcome to TDBSoverflow, Our class's own StackOverflow. Our rules:
  1. Use only meaningful and self-explanatory titles
  2. Tag your questions with meaningful keywords
  3. Use upvotes and downvotes to rate the answers
  4. When you receive a satisfying answer - Click the "V" button
Remember: you may get up to 5 bonus points to your final grade!

HAVING on aggregation

+3 votes
54 views
Hey,

All examples I've seen on HAVING is when the HAVING is on the same attribute of the aggregation.

is it possible that the having and aggregation will not be on the same attribute ?

like:

SELECT       Author.name, SUM( Wrote.url )

FROM          Author, Wrote, Mentions

WHERE       Author.login=Wrote.login AND Wrote.url=Mentions.url

GROUP BY  Author.name

HAVING      count(distinct Mentions.word) > 10000

(•Find all authors who have a vocabulary over 10000 words:)
asked Jan 31, 2018 by tikitak (2,970 points)
this is following the query in presentation 3:

SELECT       product, Sum(price * quantity)
FROM          Purchase
WHERE       date > “9/1”
GROUP BY product
HAVING      Sum(quantity) > 100

where the HAVING is not the same as the aggregation

2 Answers

+2 votes

Yes,

you can try here to change the HAVING condition to:


HAVING COUNT(Orders.EmployeeID) > 10;

or any other condition you want. Note that you have the database schema on the right so you can play with it; I find it very convenient. 

answered Jan 31, 2018 by oz (12,430 points)
+2 votes

It's possible. To understand why, please see slide 36 of the same lecture, about the order of evaluation:

1. Compute the FROM-WHERE part, obtain a table with all attributes in R1,…,Rn

2. Group by the attributes a1,…,ak

3. Compute the aggregates in C2 and keep only groups satisfying C2

4. Compute aggregates in S and return the result

So, in the 'Having' you can perform any kind of aggregation you want, on all the value you have before the projection in the Select. (Which includes 'quantity' in your example)

answered Jan 31, 2018 by Assaf (31,090 points)
But even though it's possible, I would still like to hear the staff's comment on whether this is legit/wanted.
(For example, 'group by' without aggregation is technically possible, but we were told not to use it like that (and lost points in HW2 if we did). Maybe that's also the case here)
legit - it is legit . wanted? As always, it depends. In some cases it is more efficient to push some of the WHERE clauses to after grouping.. If my memory is correct (and it is usually not) there is a slide or two about it on the presentation discussing pushing predicates etc, but someone will have to double check me as I may be wrong
...