Get Data from a single table to an empty table

I am quite new to SQL. I couldn’t find solution and I want your expertise here.

I have one table “boxes” and contains the color and type and condition. I want to get the number of types per color WHERE Condition = ‘NEW’. Thanks in advance 🙂

My tale looks like that:

           Boxes

    COLOR  TYPE  CONDITION   
    red     x        NEW
    green   y        NEW
    green   x        NEW
    blue    c        OLD
    red     b        OLD
    red     a        OLD

I want to put the data from here into another table called “ColorPerType”

expected output:

      ColorPerType

    Color  NumberOfTypes
    red        1
    green      2
    blue       0

Answer

You need a SELECT...INTO statement if ColorPerType does not exist:

select 
  color,
  sum(case when condition = 'new' then 1 else 0 end) as NumberOfTypes
into ColorPerType  
from boxes
group by color

See the demo.
If the table ColorPerType already exists use this:

insert into ColorPerType (color, NumberOfTypes)
select 
  color,
  sum(case when condition = 'new' then 1 else 0 end) as NumberOfTypes
from boxes
group by color

See the demo.

Results:
ColorPerType table

> color | NumberOfTypes
> :---- | ------------:
> blue  |             0
> green |             2
> red   |             1