Sum or count of values based on 2 column with same values in SQL server -

Sum or count of values based on 2 column with same values in SQL server -

i have db table has columns named few more other columns

colora colorb status

the info in db this.

colora colorb status --------- --------- --------- greenish null yes greenish null yes reddish greenish no reddish greenish yes

the result want depending on status='yes'

color count greenish 3 reddish 1

i have defined table hold color.

how build sql query result in output mentioned earlier? have query using left bring together , doing union not giving proper result.

this should work:

select a.color, count(a.color) count (select colora color table1 status = 'yes' , colora not null union select colorb table1 status = 'yes' , colorb not null) grouping a.color

result

| color | count | ----------------- | greenish | 3 | | reddish | 1 |

see demo

sql sql-server tsql