Which is faster: Sum(Case When) Or Group By/Count(*)?

I can write

Select 
Sum(Case When Resposta.Tecla = 1 Then 1 Else 0 End) Valor1,
Sum(Case When Resposta.Tecla = 2 Then 1 Else 0 End) Valor2,
Sum(Case When Resposta.Tecla = 3 Then 1 Else 0 End) Valor3,
Sum(Case When Resposta.Tecla = 4 Then 1 Else 0 End) Valor4,
Sum(Case When Resposta.Tecla = 5 Then 1 Else 0 End) Valor5
From Resposta

Or

Select 
    Count(*)
From Resposta Group By Tecla

I tried this over a large number of rows and it seems like taking the same time.

Anyone can confirm this?

I believe the Group By is better because there are no specific treatments. It can be optimized by the database engine. I think the results may depend on the database engine you use. Maybe the one you are using optimizes the first query anderstanding it is like a group by !

You can try the "explain / explain plan" command to see how the engine is computing your querys but with my Microsoft SQL Server 2008, I just can see a swap between 2 operations ("Compute scalar" and "agregate").

I tried such queries on a database table :

• SQL Server 2k8
• 163000 rows in the table
• 12 cathegories (Valor1 -> Valor12)

the results are quite differents :

• Group By : 2seconds
• Case When : 6seconds !

So My choice is "Group By". Another benefit is the query is simplyer to write !