CODEWITHSUNDEEP
sqlpostgresqlaggregate
Janith
Janith

Reputation: 73

Group rows with similar strings

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.

Upvotes: 7

Views: 9115

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

If you have a constant length of the appendix like in the example:

SELECT CASE right(anm_category, 3) AS animal_type  -- 3 last char
     , sum(anm_sales) AS total_sales 
FROM   x 
GROUP  BY 1;

  • You don't need a CASE statement at all, but if you use one, make it a "simple" CASE:

  • Use a positional reference instead of repeating a possibly lengthy expression.

If the length varies, but there is always a single underscore like in the example:

SELECT split_part(anm_category, '_', 2) AS animal_type  -- word after "_"
     , sum(anm_sales) AS total_sales 
FROM   x 
GROUP  BY 1;

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21905

By using PostgreSQL's split_part()

select animal||'s' animal_cat,count(*) total_sales,sum(anm_sales) sales_sum from(
select split_part(anm_cat,'_',2) animal,anm_sales from x 
)t
group by animal

sqlfiddle

By creating split_str() in MySQL

select animal||'s' animal_cat,count(*) total_sales,sum(anm_sales) sales_sum from(
select split_str(anm_cat,'_',2) animal,anm_sales from x 
)t
group by animal

sqlfiddle

Upvotes: 1

Mureinik
Mureinik

Reputation: 311338

You could group by a substr of anm_catogery:

SELECT   SUBSTR(anm_catogery, 3) || 's', COUNT(*)
FROM     x
GROUP BY anm_catogery

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

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.

Upvotes: 6

Related Questions