| 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                |
  • Apologies, I use MySql. The issue is that I need transform fruits category into one based on the year.
    – Deneezy
    Sep 15, 2022 at 9:15
  • I do have others, but these are the ones I need to merge.
    – Deneezy
    Sep 15, 2022 at 9:39

3 Answers 3


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
        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

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 ;


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.

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.

  • 1
    This query has two mistakes, first you do not filter only the Fruits Categories and second this query will fail with sql_mode ONLY_FULL_GROUP_BY enabled Sep 15, 2022 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.