CODEWITHSUNDEEP
sqlsql-server
sara
sara

Reputation: 534

How to group by for different conditions

I have table 1 as follow, I want to group the total money for prospect in one group. However, am facing difficulties to group all money in won status as in Won stage. enter image description here

I want result like below:

enter image description here

SELECT 
name,sum(value),stagename
  FROM table1
  group by name,stagename

Upvotes: 0

Views: 100

Answers (3)

cloudsafe
cloudsafe

Reputation: 2506

For multiple stagename values:

SELECT T1.name, CASE WHEN T1.STAGENAME=T2.STATUS THEN SUM(T2.VALUE) ELSE SUM(T1.value) END AS VALUE, T1.STAGENAME
  FROM table1 T1 LEFT JOIN TABLE1 T2 ON T2.STATUS=T1.STAGENAME
  group by T1.name,T2.STATUS, T1.stagename

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can query like this:

select name, stagename, sum(value) from #yourstages
    where status = 'open'
    group by name, stagename

union all

select name, status as StageName, sum(value) from #yourstages
    where status = 'won'
    group by name, status

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think you want to aggregate on a combination of the status and stagename columns:

select name,
       (case when status = 'won' then 'won' else stagename end) as stagename,
       sum(value)
from table1
group by name,
      (case when status = 'won' then 'won' else stagename end);

Upvotes: 3

Related Questions