CODEWITHSUNDEEP
sqlsql-server
user3348557
user3348557

Reputation: 89

SQL Case statement with aggregate functions

I am trying to perform a case statement where if all records for a particular Project contain a certain value for all teams then the project will be categorized as how I see fit. Something along the lines of 

case when (COUNT(team_Name) = (SUM(status = 'Completed')) then 'Complete'
case when (COUNT(team_Name) = (SUM(status = 'On Hold')) then 'On hold'
case when (COUNT(team_Name) = (SUM(status = 'Pending')) then 'Pending'
else 'In Progress' end

so I am trying to get the sum of (status = 'Completed'), which I believe is a boolean statement to match the count of team_names. My thought process is that if the count of team names and number of 'Completed' status are the same, then the project will be considered 'Complete'.

So the select grouping goes Project -> Team -> Status -> result of case statement. I want to use the above case statement in a join so the case statement results aggregates on a Project level or something along those lines.

However, my syntax seems to be a little off with the statement above and I can't get the query to run with this. I get an incorrect syntax error near '='. for the (status = 'Completed') side.

Is this even possible in SQL or is there something I need to change.

Upvotes: 0

Views: 7556

Answers (2)

Sireesh Yarlagadda
Sireesh Yarlagadda

Reputation: 13726

You can implement this using Decode Function. It works like a IF-THEN-ELSE conditon.

With an Example for you:

Select YourFieldAliasName =
CASE TEAM_ID
    WHEN 'Completed' THEN 'Complete'
WHEN 'On Hold' THEN 'Complete'
WHEN 'Pending' THEN 'Pending'
    ELSE 'Abandoned'
END

Here Abandoned is else case, if all the rest of conditions fails.

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

case COUNT(team_Name) 
    when SUM(CASE WHEN status = 'Completed' THEN 1 END) then 'Complete'
    when SUM(CASE WHEN status = 'On Hold' THEN 1 END)   then 'On Hold'
    when SUM(CASE WHEN status = 'Pending' THEN 1 END)   then 'Pending'
       else 'Abandoned' end

Upvotes: 2

Related Questions