Using case statement to group and sum

[sap1958]

select school.val

sum(case when addrlocatr <> ('d') then 1 else 0 end) as Living
from address
sample output
school.val living
business 6000
humanities 1000
mass comm 2000

Now I want to combine the school.val if the the value is humanities and mass. They should be combined into one school.al called humanities. it should be a distinct count which I belive I address with the then 1 else 0 end) procedure. Therefore the desired output should be
school.val living
business 6000
humanities 3000

 

[gmmastros]

Try something like this:

select Case When school.val In ('humanities','mass comm') 
            Then 'Humanities'
            Else school.val
            End As Val,
       sum(case when addrlocatr <> ('d') then 1 else 0 end) as Living
from   address
Group By Case When school.val In ('humanities','mass comm') 
            Then 'Humanities'
            Else school.val
            End As Val

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

SELECT CASE WHEN school.val  IN ('humanities', 'mass')
                 THEN 'humanities'
            ELSE school.val END Val,
       SUM(CASE WHEN addrlocatr <> 'd'
                     THEN 1
                ELSE 0 END) as Living
FROM Address
GROUP BY CASE WHEN school.val  IN ('humanities', 'mass')
                 THEN 'humanities'
              ELSE school.val END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

George, I have mistake, put 'mass' instead of 'mass comm'
But you have also in GROUP BY (copy and paste)

Group By Case When school.val In ('humanities','mass comm')
            Then 'Humanities'
            Else school.val
            End [COLOR=red]As Val[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Nice one Boris.

Unfortunately, I left an 'As Val' in my group by, so mine will probably error.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom