SQL - How to Categorize a Case Statement?-CodePudding

In this sample dataset, I have some Colors : "Red" and "Blue" and some Fruit: "Apple" and "Grapes" associated with a person/name.

My goal is to add a column for each of these groupings, called "Colors" and Fruit. These newly added columns would count 1 whenever the value is > 0 for each of their respective grouping.

For example if either "Red" or "Blue" contain a value > 0, we will add 1 to Colors. For John Smith, since both the Red and Blue columns contain a value > 0, the Colors column will be 2. Here is the expected output:

I know case statements can add 1 or 0, but how do we do this when we have 2 columns per grouping?

CodePudding user response：

You do not need in CASE.

SELECT blue, red, (blue > 0)   (red > 0) colors,
       apple, grapes, (apple > 0)   (grapes > 0) fruit
FROM source_table

The values in source table shouldn't be NULL.

CodePudding user response：

SELECT
  *,
  ((CASE WHEN apple > 0 THEN 1 ELSE 0 END)   (CASE WHEN WHEN grapes > 0 THEN 1 ELSE 0 END)) as fruit,
  ((CASE WHEN red > 0 THEN 1 ELSE 0 END)   (CASE WHEN blue > 0 THEN 1ELSE 0 END)) as colors
from table

CodePudding user response：

assuming the source data is in a pivoted state

with cte as (
select 'john' as name, 2 as blue, 3 as red, 2 as apple, 4 as grape union all
select 'tom', 0 , 8, 0, 0
)
select
      name
    , blue
    , red
    , coalesce((blue > 0),0) 
        coalesce((red > 0),0)
      as colours
    , apple
    , grape
    , coalesce((apple > 0),0) 
        coalesce((grape > 0),0)
      as fruits
from cte

name | blue | red | colours | apple | grape | fruits
:--- | ---: | --: | ------: | ----: | ----: | -----:
john |    2 |   3 |       2 |     2 |     4 |      2
tom  |    0 |   8 |       1 |     0 |     0 |      0

db<>fiddle here