Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is it possible to group according to some kind of mapping?

I have a table in BigQuery where I would like to be able to group according to some type of mapping file, for example if I had the following

Product Code Country Sales
AA1 US 5
AA2 US 10
AA1 UK 15
BB1 US 20
BB2 US 25
BB2 UK 25

and the following mapping
[AA1, AA2] > AA1
[BB1, BB2] > BB1

I would ideally be looking to get something along the following:

Product Code Country Sales
AA1 US 15
AA1 UK 15
BB1 US 45
BB1 UK 25

except obviously on a much larger scale. I’m wondering if there is a way to do this purely in SQL

Tags:

2

Answers


  1. 0

    You may aggregate using a CASE expression, which does the mapping:

    SELECT
    CASE WHEN ProductCode IN ('AA1', 'AA2') THEN 'AA1'
         WHEN ProductCode IN ('BB1', 'BB2') THEN 'BB1' END AS ProductCode,
    Country,
    SUM(Sales) AS Sales
FROM yourTable
GROUP BY 1, 2;

    If you have a more complex mapping than this, then it might be better to create a bona-fide table which contains all the mappings, then join to this from your main table.

    Login or Signup to reply.
  2. 0

    Employing a mapping table appears to be a simple join eg

    drop table if exists t,mapping;

create table t
(ProductCode varchar(3),    Country varchar(2), Sales int);
insert into t values
('AA1', 'US'    ,5),
('AA2', 'US'    ,10),
('AA1', 'UK'    ,15),
('BB1', 'US'    ,20),
('BB2', 'US'    ,25),
('BB2', 'UK'    ,25);

create table mapping
(productcode varchar(3),grp varchar(3));
insert into mapping values
('aa1','aa1'),('aa2','aa1'),('bb1','bb1'),('bb2','bb1');

SELECT
     grp,
    Country,
    SUM(Sales) AS Sales
FROM t
join mapping on t.ProductCode = mapping.productcode
GROUP BY grp,country desc;

+------+---------+-------+
| grp  | Country | Sales |
+------+---------+-------+
| aa1  | US      |    15 |
| aa1  | UK      |    15 |
| bb1  | US      |    45 |
| bb1  | UK      |    25 |
+------+---------+-------+
4 rows in set (0.001 sec)

    But you are losing the fact that different products can appear within a group.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search