Group rows with similar strings

Question

I have searched a lot, but most of solutions are for concatenation option and not what I really want.

I have a table called X (in a Postgres database):

anm_id  anm_category anm_sales
1       a_dog        100
2       b_dog        50
3       c_dog        60
4       a_cat        70
5       b_cat        80
6       c_cat        40

I want to get total sales by grouping 'a_dog', 'b_dog', 'c_dog' as dogs and 'a_cat', 'b_cat', 'c_cat' as cats.

I cannot change the data in the table as it is an external data base from which I am supposed to get information only.

How to do this using an SQL query? It does not need to be specific to Postgres.

Answer 1

Use case statement to group the animals of same categories together

SELECT CASE 
         WHEN anm_category LIKE '%dog' THEN 'Dogs' 
         WHEN anm_category LIKE '%cat' THEN 'cats' 
         ELSE 'Others' 
       END            AS Animals_category, 
       Sum(anm_sales) AS total_sales 
FROM   yourtables 
GROUP  BY CASE 
            WHEN anm_category LIKE '%dog' THEN 'Dogs' 
            WHEN anm_category LIKE '%cat' THEN 'cats' 
            ELSE 'Others' 
          END 

Also this query should work with most of the databases.