CODEWITHSUNDEEP
sqloracle-databaseoracle10g
spider8
spider8

Reputation: 157

aggregations in case statement

I am trying to do aggregations in case statement. I found 2 ways to do it. Can anyone say what the difference between the 2 is?

  1. (CASE WHEN Event = 5 THEN count(*) ELSE 0 END ) Follow_Count GROUP BY Event;

  2. SUM(CASE Event WHEN 5 THEN 1 ELSE 0 END) AS Follow_Count

Upvotes: 2

Views: 234

Answers (3)

Craig
Craig

Reputation: 5820

Your case 1 will produce a row for each event in the table (from your group by). Your case 2 will just return 1 row.

Is there a reason that you wouldn't just write:

select count(*)
from my_table
where event = 5;

Upvotes: 5

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Better would be:

count(CASE Event WHEN 5 THEN 1 END) AS Follow_Count

Because 1) for count used own standart counter, 2) "else" not need (count don't count nulls)

Regards, Sayan M.

Upvotes: 3

zerkms
zerkms

Reputation: 254916

There is no significant difference. You can decide for you which is better by comparing their execution plans.

Upvotes: 2

Related Questions