Is using SUM() twice suboptimal?

Question

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

SELECT  id,
  sum(hours) AS totalhours
  FROM mytable
  GROUP BY id
  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)

score 3 · Accepted Answer · answered Jun 14 '13 at 20:05

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.

score 0 · Answer 2 · answered May 28 '13 at 15:33

Compare your query

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

To this equivalent one an check in what they differ

explain
select *
from (
    select sum(counttodo) counttodo
    from orderline
    group by orderlineid
) s
where counttodo > 100

score 0 · Answer 3 · answered May 29 '13 at 16:47

0

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:

SELECT id,
FROM mytable
GROUP BY id
HAVING sum(hours) > 50;

answered May 29 '13 at 16:47

Colin 't Hart

9,455
15
36
44

Is using SUM() twice suboptimal?

3 Answers3