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.

The best solution depends on exact details. Your example may be over-simplified. — Erwin Brandstetter, Jul 18 '15 at 16:52

Pரதீப் · Accepted Answer · 2015-07-18T04:40:56.843

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.

Vivek S. · Answer 2 · 2015-07-18T06:59:09.463

1

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

edited Jul 18 '15 at 06:59

answered Jul 18 '15 at 05:10

Vivek S.

19,945
7
68
85

You are missing the sum of anm_sales column, see here instead http://sqlfiddle.com/#!15/fa516/5 . – Carsten Massmann Jul 18 '15 at 05:55

Mureinik · Answer 3 · 2015-07-18T05:19:20.377

0

You could group by a substr of anm_catogery:

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

edited Jul 18 '15 at 05:19

answered Jul 18 '15 at 03:50

Mureinik

297,002
52
306
350

@wingedpanther indeed, how embarrassing :-( Fixed. Thanks for noticing! – Mureinik Jul 18 '15 at 05:19
You forgot the alias name `anm_catogery` after the first column. And, who is to tell you that the '_dog' or '_cat' portion starts after the first character? – Carsten Massmann Jul 18 '15 at 05:43

score 0 · Answer 4 · edited May 23 '17 at 11:43

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:
- Simplify nested case when statement
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;

Group rows with similar strings

4 Answers4