Merge 2 rows into one row

Question

| Year | Category | Number_of_products |
| 19   |Fruits1   | 25                 |
| 19   |Fruits2   | 45                 |
| 20   |Fruits1   | 50                 |
| 20   |Fruits2   | 50                 |

Do you know how to transform this table, so it would like this? Would appreciate any help

| Year | Category | Number_of_products |
|------|----------|--------------------|
|19    |Fruits    | 70                 |
|20    |Fruits    | 100                |

Answer 1

I think it works in SQL Server. If you use MySql you can change the syntax to be compatible with your system:

with cte as
        (select 
        year,
        case when Category='Fruits1' then 'Fruits'
            when Category='Fruits2' then 'del'
            else Category end as Category,
        case when Category='Fruits1' then Number_of_products + (select Number_of_products from table1 as t2 where t2.year=t1.year and t2.Category='Fruits2' )
             when Category='Fruits2' then -1
             else Number_of_products end as value
        from table1  as t1)
    select * 
    from cte
    where value>0

Answer 2

You can use:

select `Year`,
       LEFT(Category,6) as new_Category,
       sum(Number_of_products) as tot_Number_of_products
from test_tbl
where LEFT(Category,6) ='Fruits' ---this could be written as  " Category like 'Fruits%' "
group by `Year`,new_Category
order by `Year` asc ;

https://dbfiddle.uk/3pZ2JRve

What I recommend.

1. Do not use Keywords and Reserved Words such as YEAR, a better practice would be renaming if possible.
2. In the Category column you have values like Fruits1,Fruits2 it seems excess and you could group them on a main category such as Fruits.

Answer 3

It's not clear if you want to perform a query or alter the table structure. Anyway, you could select data like this: select "year", 'Fruits', sum(number_of_products)as number_of_products from schema.table group by year

Consider that in that case, the value 'Fruits' was not clear how i would like to procedd to define him.