CODEWITHSUNDEEP
sqlgroup-byhaving-clause
Mando
Mando

Reputation: 11712

How to avoid duplicating statements with grouping functions?

I have a t-sql query where sum function is duplicated. How to avoid duplicating those statements?

select 
    Id,
    sum(Value)
from
    SomeTable
group by 
    Id
having
    sum(Value) > 1000

Upvotes: 1

Views: 60

Answers (3)

Lajos Veres
Lajos Veres

Reputation: 13725

It look like table aliasing is not supported. I think with should work:

with tmptable (id,sumv)
as
(select
    Id,
    sum(Value) as sumv
from
    SomeTable
group by 
    Id  
)
select
    id,
    sumv 
from
    tmptable
where
   sumv>1000

And a fiddle:

http://sqlfiddle.com/#!6/0d3f2/2

Upvotes: 1

user1207705
user1207705

Reputation: 43

You need to remove the sum(Value) from the group by clause.

Upvotes: 1

RB.
RB.

Reputation: 37202

You can use GROUP BY 1, 2 to group by (in this case) the first and second columns, and thus avoid duplication in the GROUP BY clause.

Upvotes: 0

Related Questions