Get count column if at least one value exist - SQL

Question

How to create a separate column for each color if at least one value exists in a group by clause? Does each color column need a separate select statement with group by clause. Thanks in advance.

For example

    SELECT
    COUNT(ColorID) AS IcdCodecount,
    Colorname
    -- Blue -> if at least one value exists in 1, 10, 12
    -- Red -> if at least one value in 0, 3, 4, 15
    -- White -> if at least one value in  11, 12, 13, 14
    -- Yellow -> if at least one value in 20, 21, 22, 23
    FROM
      TestTable
    WHERE ColorID IN (
                      1, 10, 12, -- blue
                      0, 3, 4, 15, -- Red
                      11, 12, 13, 14, -- White
                      20, 21, 22, 23, -- yellow)
    GROUP BY Colorname

Tag your question with the database you are using. And what results do you want if there are multiple colors? — Gordon Linoff
– Gordon Linoff, Commented Jun 19, 2020 at 1:00
What database are you using? Try looking into pivot table if your db supports it. — John
– John, Commented Jun 19, 2020 at 1:05

Gordon Linoff · Accepted Answer · 2020-06-19 01:02:11Z

1

You can use conditional aggregation:

(case when sum(case when colorId in (1, 10, 12) then 1 else 0 end)  > 0 then 1 else 0 end) as is_blue,
(case when sum(case when colorId in (0, 3, 4, 15) then 1 else 0 end)  > 0 then 1 else 0 end) as is_red,
(case when sum(case when colorId in (11, 12, 13, 14) then 1 else 0 end)  > 0 then 1 else 0 end) as is_white,
(case when sum(case when colorId in (20, 21, 22, 23) then 1 else 0 end)  > 0 then 1 else 0 end) as is_yellow

answered Jun 19, 2020 at 1:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wan Over a year ago

Thank you Gordon. it's a SQL db. Trying to get a separate column for each color if at least one value exists. Case statement with sum will work.

Gordon Linoff Over a year ago

@IYO . . . That is what this does.

John · Accepted Answer · 2020-06-19 01:07:38Z

0

Try using a pivot table. Implementation for Oracle is described here:

https://www.oracletutorial.com/oracle-basics/oracle-pivot/

answered Jun 19, 2020 at 1:07

John

4,0645 gold badges41 silver badges74 bronze badges

Collectives™ on Stack Overflow

Get count column if at least one value exist - SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related