MVachelard MVachelard - 7 months ago 10
SQL Question

SQL - Sum two columns group by ID

I would like to sum two columns "Immo"+"Conso" group by "ID" in order to create a new variable "Mixte". My new variable "Mixte" is as follow:


  • if one ID has (at least) 1 in "Immo" AND 1 in "Conso" then "Mixte" is yes, otherwise "Mixte" is no.



For exemple:

Ident | Immo | Conso | Mixte
---------------------------------
1 | 0 | 1 | yes
1 | 1 | 0 | yes
2 | 1 | 0 | no
3 | 0 | 1 | no
3 | 0 | 1 | no
3 | 0 | 1 | no
4 | 0 | 1 | yes
4 | 0 | 1 | yes
4 | 1 | 0 | yes


Thank you for helping me. Do not hesitate to ask me questions if I wasn't clear.

Answer
select ident,result=(case when sum(Immo)>0 and sum(Conso)>0 then 'yes' 
               else 'no' end)
from tabname (NOLOCK)
group by id