Clever Geek Handbook

Group lines with similar lines

I've searched a lot, but most of the solutions are for the concatenation option, not what I really want.

I have a table called X

(in 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 the 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 database from which I only have to get information.

How can I do this using a SQL query? This is optional for Postgres.

+3


source to share


4 answers


Use the operator case

to group animals of the same category 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 databases.

+1


source


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

+1


source


You can group by substr

from anm_catogery

:

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


source


If you have a constant application length 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 an operator at all CASE

    , but if you use one, keep it "simple" CASE

    :

  • Use a positional reference instead of repeating the longest possible expression.

If the length changes, but there is always one underscore, as 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;
0


source






More articles:


All Articles