I have a table that contains several thousand products with the following columns:

• scores: includes the quality of a score from 1-100
• y_2018: the product was in service during 2018
• y_2020: the product was in service during 2020
• y_2021: the product was in service during 2021

My objective is to categorize products by the different years that they have been in service. This would mean there would be a "1/3" column for products in service only in 2018, only in 2020, and only in 2021. There would also be a "2/3" column for products in service in 2018 and 2020 but not 2021, 2018 and 2021 but not 2020, and 2020 and 2021 but not 2018. Lastly there would be a "3/3" column for the products that have been in service for 2018, 2020, and 2021. This is the code I have thusfar:

SELECT 
case
when (case scores)>=0 and scores<30 then '0-30'
when (case scores)>=30 and scores<70 then '30-70'
when (case scores)>=70 and scores<100 then '70-100'
ELSE NULL end as "Score Range"
,COUNT(CASE WHEN y_2018 IS NOT NULL AND y_2020 IS NULL AND y_2021 IS 
NULL) THEN 1 ELSE 0 END) 
  
COUNT(CASE WHEN y_2018 IS NULL AND y_2020 
IS NOT NULL AND y_2021 IS NULL) THEN 1 ELSE 0 END) 
 
COUNT(CASE WHEN y_2018 IS NULL AND y_2020 
IS NULL AND y_2021 IS NOT NULL) THEN 1 ELSE 0 END) AS "1/3"
,COUNT(CASE WHEN y_2018 IS NOT NULL AND y_2020 IS NOT NULL AND y_2021 IS 
NULL) THEN 1 ELSE 0 END) 
  
COUNT(CASE WHEN y_2018 IS NULL AND y_2020 
IS NOT NULL AND y_2021 IS NOT NULL) THEN 1 ELSE 0 END) 
 
COUNT(CASE WHEN y_2018 IS NOT NULL AND y_2020 
IS NULL AND y_2021 IS NOT NULL) THEN 1 ELSE 0 END) AS "2/3"
,COUNT(CASE WHEN y_2018 IS NOT NULL AND y_2020 IS NOT NULL AND y_2021 IS 
NOT NULL) THEN 1 ELSE 0 END) AS "3/3"
FROM table
GROUP BY 1
ORDER BY 1

I think I'm close and my current output looks like this:

Score Range  |  1/3    2/3   3/3
70-100       |  2000   2000   500
30-70        |  1000   1000   300
0-30         |  1500   1500   400

The main question I have is why the "1/3" and "2/3" are the same and how can I adjust my query to get what I'm looking for?

CodePudding user response：

I don't know if I'm understanding this correctly, but if you want a column for each year they were in service, you can just repeat your existing count with minimal editing.

,COUNT(CASE
WHEN y_2018 IS NOT NULL THEN 1
WHEN y_2020 IS NOT NULL THEN 1
WHEN y_2021 IS NOT NULL THEN 1
ELSE NULL END) AS "COUNT"
,COUNT(CASE
WHEN y_2018 IS NOT NULL and y_2020 IS NOT NULL and y_2021 IS NOT NULL THEN 1
ELSE NULL END) AS "COUNT all"
,COUNT(CASE
WHEN y_2020 IS NOT NULL and y_2021 IS NOT NULL and y_2018 IS NULL THEN 1
ELSE NULL END) AS "COUNT 2020 & 2021"
,COUNT(CASE
WHEN y_2021 IS NOT NULL and y_2020 IS NULL and y_2018 IS NULL THEN 1
ELSE NULL END) AS "COUNT 2021"

CodePudding user response：

You need to make three different columns with three different conditions like below:

SELECT 
case
when (case scores)>=0 and scores<30 then '0-30'
when (case scores)>=30 and scores<70 then '30-70'
when (case scores)>=70 and scores<100 then '70-100'
ELSE NULL end as "Score Range"
,SUM(CASE WHEN y_2021 IS NOT NULL AND y_2020 IS NULL AND y_2018 IS NULL THEN 1 ELSE 0 END) AS "y_2021_COUNT"
,SUM(CASE WHEN y_2021 IS NOT NULL AND y_2020 IS NOT NULL AND y_2018 IS NULL THEN 1 ELSE 0 END) AS "y_2021_2020_COUNT"
,SUM(CASE WHEN y_2021 IS NOT NULL AND y_2020 IS NOT NULL AND y_2018 IS NOT NULL THEN 1 ELSE 0 END) AS "y_2021_2020_2018_COUNT"
FROM table
GROUP BY 1