CODEWITHSUNDEEP
sqlsql-server
Saleem Khan
Saleem Khan

Reputation: 749

group by specific values in different fields SQL Server

I have a table that looks like this (sorry but don't know how to put things in a table format):

  |  ID| Status        | Business Area |Business sub area| ...|
1 | ongoing| Area 1        | Null            | ...|
2 | closed | Area 2        | Sub-area a      | ...|
...

I need to count all the cases with a different criteria for each column (a count based on the status) but grouped by specific values in either the business area or the business sub area.

The table I want would have the following structure

Groups    | total | count of ongoing | count of closed |
Area 1    | 10    | 5                | 5               |
Sub-area A| 6     | 3                | 3               |

My issue isn't really to do with getting the columns as I can work that out, but I don't know if it's possible to group the records in the way i've done above.

For my table there will be values in the business area field that I don't want records to group under and likewise for the sub area group which is why i want a way for the groups to be specific values rather than entire fields.

How would I group by specific values that exist in different fields in SQL Server?

Upvotes: 0

Views: 1152

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If I understand correctly, you can use COALESCE():

select coalesce(business_sub_area, business_area) as groups,
       . . .
. . .
group by coalesce(business_sub_area, business_area);

Upvotes: 1

Related Questions