I know I have to write SUM twice, if I wish to use it in a HAVING clause (or use a derived table otherwise):

  sum(hours) AS totalhours
  FROM mytable
  HAVING sum(hours) > 50;

My question now is, whether or not this is suboptimal. As a programmer, this query looks like the DB will calculate the sum twice. Is that so, or should I rely on optimizations the DB engine will do for me?

Update: an explain of a comparable query:

postgres=> explain select sum(counttodo) from orderline group by orderlineid having sum(counttodo) > 100;
                             QUERY PLAN                             
 HashAggregate  (cost=1.31..1.54 rows=18 width=8)
   Filter: (sum(counttodo) > 100)
   ->  Seq Scan on orderline  (cost=0.00..1.18 rows=18 width=8)
(3 rows)
  • can you post the explain?
    – Ste
    May 28, 2013 at 14:11
  • I won't answer this as there is a nice, concise explanation here: " In case you're wondering why you can't refer to the SELECT aliases later in the query, like in the WHERE clause, it's all to do with order of evaluation. SQL isn't evaluated in the order it's written. The SELECT-list is actually evaluated almost last, and its contents effectively don't exist yet when HAVING etc are processed. This becomes important when the select-list includes functions with side-effects [...]"
    – dezso
    May 28, 2013 at 15:02
  • ... this is why you can't just reference the aggregated column in the HAVING clause - but, to my understanding, internally this is rather done the other way around.
    – dezso
    May 28, 2013 at 15:05
  • 2
    @BartFriederichs well, many people complain alongside these lines (I had complained as well until I did not get used to it...) It is not executed twice and probably could be done by using an alias in HAVING (and then pulling the column definition from the SELECT clause) - for some reason they just don't do that.
    – dezso
    May 28, 2013 at 15:24
  • 3
    I think that letting the DB engine worry about optimisations ought to be second nature to an RDBMS practitioner. SQL is a 4GL, so we're defining the result set we want, not the means by which it is achieved. There are many other issues out there that we do not worry over for the most part -- join order, or transformation of EXISTS into a join for example. This particular issue is more a problem from the "Don't Repeat Yourself" perspective for complex expression, but sensible workarounds (in-line views, CTEs) can help with those. May 28, 2013 at 16:54

3 Answers 3


The sum is only computed once.

I verified this using

create table mytable (id int, hours int);
insert into mytable values (1, 60);
select sum(hours) from mytable group by id having sum(hours) > 50;

and then used a debugger to check how many times int4_sum (the transition function behind the sum aggregate) was called: once.


Compare your query

select sum(counttodo)
from orderline
group by orderlineid
having sum(counttodo) > 100

To this equivalent one an check in what they differ

select *
from (
    select sum(counttodo) counttodo
    from orderline
    group by orderlineid
) s
where counttodo > 100
  • 1
    I can see what you're getting at but in its current form this doesn't make for a "good" answer. Post the explain for each with a little more commentary and you're good for some upvotes. May 28, 2013 at 21:32

You don't have to write SUM twice if you don't need to retrieve it; if you're only interested in the ids having a SUM(hours) > 50 then the following is perfectly valid:

FROM mytable
HAVING sum(hours) > 50;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.