Conditional counting in a group 1

[nsanto17]

Below is my table structure after joinning two tables in an SQL View. How the view is already grouped by name but i want to add a column showing how many status '5' rows there are for each group. I cannot figure out how to do this. I have to be missing something. I know it has be in the Select Statement but i am having a hard time with the syntax.

Id | Status | Name
1 5 John
2 5 John
3 9 Susan
4 5 Susan

I would like my results to show
Name | Status 5
John 2
Susan 1

Thanks in advance.

 

[imex]

Try something like this:

select Name, SUM(CASE WHEN Status = 5 THEN 1 ELSE 0 END) as [Status 5]
from MyTable
group by Name

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[nsanto17]

Worked like a charm... Thanks...

 

[nsanto17]

Is it possiable to add an Avg to this.

id | Name | Status | Score
1 John 5 100
2 John 5 300
3 Susan 2 500
4 Susan 5 100

Grouped by Name

I want the avergae score when status = 5??


Thanks in advnace

 

[imex]

Try:

select 
    Name, 
    SUM(CASE WHEN Status = 5 THEN 1 ELSE 0 END) as [Status 5],
    SUM(CASE WHEN Status = 5 THEN Score else 0 END) / 
    SUM(CASE WHEN Status = 5 THEN 1 ELSE 0 END) as [Avg Status 5]
from MyTable
group by Name

Hope this help.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imoveis-venda-são_paulo-residencial-apartamento[/url]

 

[drlex]

If there can be a name with no status 5 scores, then a "Divide by zero error" will occur; how about wrapping the query up to test for zeros when calculating the average?

SELECT 
   [Name],
   [Status 5], 
   CASE WHEN [Status 5] <>0 THEN [Status 5 Scores]/[Status 5] ELSE NULL END AS [Status 5 Average]
FROM
(
 SELECT [Name], SUM(CASE WHEN [Status] = 5 THEN 1 ELSE 0 END) as [Status 5],
 SUM(CASE WHEN [Status] = 5 THEN Score ELSE 0 END) AS [Status 5 Scores] 
 FROM MyTable GROUP BY [Name]
)T1

N.B. Average will be an integer calculation unless you force a decimal calculation - e.g.

CASE WHEN [Status 5] <>0 THEN [COLOR=#EF2929][b]1.0*[/b][/color][Status 5 Scores]/[Status 5] ELSE NULL END AS [Average]

soi là, soi carré