Joining three tables and create new column-CodePudding

Have to find: In which department of store has the max bonus card uses ( it can be rank also)

I have data like below

            Categories
|----------------|--------------------|
|     **ID**     |**product_category**   
|----------------|--------------------|
|         1      |    sweets    
|----------------|--------------------|
|         2      |  kitchen utensils   
|----------------|--------------------|
                Stores
|----------------|--------------------|
|  **store_id**  |   **store_name**   
|----------------|--------------------|
|         a11    |        ABC    
|----------------|--------------------|
|         a22    |        DFG   
|----------------|--------------------|

               Sales
|--------------|----------|-----------|
|*product_code*|bonus_card| *store_id**  
|------------- |----------|---------- |
|    3334      |    TRUE  |   a11          
|------------- |----------|--- ------ |
|     8388     |   FALSE  |   a22  
|--------------|----------|-----------|

                     Products
|-------------|--------------|-----------|--------------|
|*produ_code* |*product_name*|category_id| *store_id**  
|-------------|--------------|-----------|------------- |
|   3334      |    AAA       |     1     |   a11    
|-------------|--------------|--- -------|--------------|
|    8388     |     BBB      |     2     |    a22   
|-------------|--------------|---------- |------------- |

in sales table true-mean,yes uses and false-not uses the bonus card

i tried smth

SELECT  category.product_category, store.store_id
inner join product1
on product1.product_category=product_category;
inner join category
on product1.category_id=ID;

but i couldn'y go on..

I appreciate every help!

Best Regards.

CodePudding user response：

First you need to have the number of sales with bonus card. I don't know if you need it as a total or as a percentage but I'm assuming you want the maximum total sales.

select store_name, product_category, count(1) as sales_made from (Select * from sales where bonus_Card = true) a
inner join products b on (a.product_code = b.product_code)
inner join categories c on (b.category_id = c.category_id)
inner join stores d on (b.store_id = d.store_id)
group by store_name, product_category

Then, considering what's above, it depends on if you need this globally or per store. You will have to partition by store_name if you need it by store.

select * from (
select a.*, row_number() over (/*partition by store_name*/ order by sales_made) as rn from result)
where rn = 1

Full query:

with results as (select store_name, product_category, count(1) as sales_made from (Select * from sales where bonus_Card = true) a
    inner join products b on (a.product_code = b.product_code)
    inner join categories c on (b.category_id = c.category_id)
    inner join stores d on (b.store_id = d.store_id)
    group by store_name, product_category)


    select * from (
    select a.*, row_number() over (/*partition by store_name*/ order by sales_made desc) as rn from result)
    where rn = 1

CodePudding user response：

You can do it with a simple INNER JOIN and a SUM aggregation function, like this:

SELECT 
    Stores.store_name,
    SUM(Sales.bonus_card) AS num_bonus_cards 
FROM 
    Sales
INNER JOIN
    Stores
ON
    Sales.store_id = Stores.store_id
GROUP BY
    Stores.store_name
ORDER BY 
    num_bonus_cards DESC
LIMIT 1

Here's the SQL Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0de7fb2c7e7503e991f998fab82ad8e3.