Compare and calculate ,product and and category

Get Help
1

i have the database that contains all the sales of soft drinks
and the database contains all the sales of Pepsi
how to combine this data in same question

here is sql
that gives me
all the sales in category Soft drinks

SELECT `Category Translations`.`name` AS `name`, `Category`.`id` AS `id`, count(*) AS `count`
    FROM `orders`
    LEFT JOIN `order_details` `Order Details` ON `orders`.`id` = `Order Details`.`order_id` 
   LEFT JOIN `products` `Product` ON `Order Details`.`product_id` = `Product`.`id` LEFT 
   JOIN `product_translations` `Product Translations` ON `Product`.`id` = `Product 
    Translations`.`product_id` LEFT JOIN `categories` `Category` ON `Product`.`category_id` = `Category`.`id` LEFT JOIN `category_translations` `Category Translations` ON `Category`.`id` = `Category Translations`.`category_id` LEFT JOIN `store_products_quantity` `Store Products Quantity` ON `Product`.`id` = `Store Products Quantity`.`product_id`
WHERE (`orders`.`status` = 4
   AND `Category Translations`.`locale` = 'en' AND date(`orders`.`schedule_date`) BETWEEN date(date_add(now(), INTERVAL -30 day)) AND date(date_add(now(), INTERVAL -1 day)) AND `Store Products Quantity`.`store_id` = 6 AND `Category Translations`.`name` = 'Soft drinks')
GROUP BY `Category Translations`.`name`, `Category`.`id`
ORDER BY `count` DESC, `Category Translations`.`name` ASC, `Category`.`id` ASC

here is the SQL that gives me pepsi sales for the same period

SELECT `Category Translations`.`name` AS `name`, `Category`.`id` AS `id`, count(*) AS `count`
FROM `orders`
LEFT JOIN `order_details` `Order Details` ON `orders`.`id` = `Order Details`.`order_id` LEFT JOIN `products` `Product` ON `Order Details`.`product_id` = `Product`.`id` LEFT JOIN `product_translations` `Product Translations` ON `Product`.`id` = `Product Translations`.`product_id` LEFT JOIN `categories` `Category` ON `Product`.`category_id` = `Category`.`id` LEFT JOIN `category_translations` `Category Translations` ON `Category`.`id` = `Category Translations`.`category_id` LEFT JOIN `store_products_quantity` `Store Products Quantity` ON `Product`.`id` = `Store Products Quantity`.`product_id`
WHERE (`orders`.`status` = 4
   AND `Category Translations`.`locale` = 'en' AND date(`orders`.`schedule_date`) BETWEEN date(date_add(now(), INTERVAL -30 day)) AND date(date_add(now(), INTERVAL -1 day)) AND `Store Products Quantity`.`store_id` = 6 AND (`Product Translations`.`name` = 'بيبسى اكشن'
    OR `Product Translations`.`name` = 'بيبسى اكشن عرض x6 ق' OR `Product Translations`.`name` = 'بيبسي  مشروب مياه غازية -1لتر + 25 ملي زيادة' OR `Product Translations`.`name` = 'بيبسي 2 لتر' OR `Product Translations`.`name` = 'بيبسي تربو' OR `Product Translations`.`name` = 'بيبسي دايت تربو' OR `Product Translations`.`name` = 'بيبسي دايت كان' OR `Product Translations`.`name` = 'بيبسي دايت لتر' OR `Product Translations`.`name` = 'بيبسي عائلي - حجم أكبر' OR `Product Translations`.`name` = 'بيبسي كان' OR `Product Translations`.`name` = 'بيبسي كان جيب' OR `Product Translations`.`name` = 'بيبسي لتر' OR `Product Translations`.`name` = 'بيبسي ليمون كان' OR `Product Translations`.`name` = 'بيبسي ليمون لتر' OR `Product Translations`.`name` = 'بيبسي ميني تربو' OR `Product Translations`.`name` = 'ميرندا برتقال تيربو' OR `Product Translations`.`name` = 'ميرندا برتقال جيب' OR `Product Translations`.`name` = 'ميرندا برتقال جيب عرض 3ق' OR `Product Translations`.`name` = 'ميرندا برتقال عائلي' OR `Product Translations`.`name` = 'ميرندا برتقال كان' OR `Product Translations`.`name` = 'ميرندا برتقال لتر' OR `Product Translations`.`name` = 'ميرندا برتقال ميني تيربو' OR `Product Translations`.`name` = 'ميرندا تفاح اخضر زجاجه الهضبه' OR `Product Translations`.`name` = 'ميرندا تفاح اخضر ميني تربو' OR `Product Translations`.`name` = 'ميرندا تفاح تيربو' OR `Product Translations`.`name` = 'ميرندا تفاح عائلي' OR `Product Translations`.`name` = 'ميرندا تفاح كان' OR `Product Translations`.`name` = 'ميرندا تفاح كان جيب' OR `Product Translations`.`name` = 'ميرندا تفاح كان عرض 3ق' OR `Product Translations`.`name` = 'ميرندا تفاح لتر' OR `Product Translations`.`name` = 'سفن أب تربو' OR `Product Translations`.`name` = 'سفن أب تربو - عرض x6 ق' OR `Product Translations`.`name` = 'سفن أب عائلي' OR `Product Translations`.`name` = 'سفن أب كان' OR `Product Translations`.`name` = 'سفن أب كان جيب' OR `Product Translations`.`name` = 'سفن أب كان جيب عرض 3 ق' OR `Product Translations`.`name` = 'سفن أب لتر' OR `Product Translations`.`name` = 'سفن أب لتر دايت' OR `Product Translations`.`name` = 'سفن أب ميني تربو' OR `Product Translations`.`name` = 'سفن اب دايت' OR `Product Translations`.`name` = 'سفن اب دايت - كان' OR `Product Translations`.`name` = 'سفن اب زجاجه الهضبه 350 مل' OR `Product Translations`.`name` = 'Aquafina Gallon Water, 18.9 Liter' OR `Product Translations`.`name` = 'Aquafina natural water' OR `Product Translations`.`name` = 'Aquafina natural water - box' OR `Product Translations`.`name` = 'Aquafina natural water - box-S' OR `Product Translations`.`name` = 'سفن اب عائلي أكبر') AND `Category Translations`.`name` = 'Soft drinks')
GROUP BY `Category Translations`.`name`, `Category`.`id`
ORDER BY `count` DESC, `Category Translations`.`name` ASC, `Category`.`id` ASC

i want the question to be common A all the sales of Soft drinks category column b all the sales of Pepsi

column C is B/A%

2

Hi @smrasmy

There's currently a bug preventing you from using the GUI to join these two questions:
https://github.com/metabase/metabase/issues/12928 - upvote by clicking :+1: on the first post
And you cannot use Saved Question variable references because of this issue:
https://github.com/metabase/metabase/issues/12236 - upvote by clicking :+1: on the first post
It would likely have helped if there was a way to count distinct by field:
https://github.com/metabase/metabase/issues/14523 - upvote by clicking :+1: on the first post

You would have to create a SQL question like this - this is an example, so if it doesn't work, then you will have to adjust it:

WITH alldrinks AS (
    SELECT `category_translations`.`name` AS `name`,
           `categories`.`id` AS `id`,
           Count(*) AS `count`
    FROM `orders`
    LEFT JOIN `order_details` ON `orders`.`id` = `order_details`.`order_id`
    LEFT JOIN `products` ON `order_details`.`product_id` = `products`.`id`
    LEFT JOIN `product_translations` ON `products`.`id` = `product translations`.`product_id`
    LEFT JOIN `categories` ON `products`.`category_id` = `categories`.`id`
    LEFT JOIN `category_translations` ON `categories`.`id` = `category_translations`.`category_id`
    LEFT JOIN `store_products_quantity` ON `products`.`id` = `store_products_quantity`.`product_id`
    WHERE (`orders`.`status` = 4
           AND `category_translations`.`locale` = 'en'
           AND Date(`orders`.`schedule_date`) BETWEEN Date(Date_add(Now(), INTERVAL -30 DAY)) AND Date(Date_add(Now(), INTERVAL -1 DAY))
           AND `store_products_quantity`.`store_id` = 6
           AND `category_translations`.`name` = 'Soft drinks') GROUP  BY `category_translations`.`name`,
                                                                         `categories`.`id`
      ORDER  BY `count` DESC, `category_translations`.`name` ASC, `categories`.`id` ASC
)
, justpepsi AS (
    SELECT `category_translations`.`name` AS `name`,
           `categories`.`id` AS `id`,
           count(*) AS `count`
    FROM `orders`
    LEFT JOIN `order_details` ON `orders`.`id` = `order_details`.`order_id`
    LEFT JOIN `products` ON `order_details`.`product_id` = `products`.`id`
    LEFT JOIN `product_translations` ON `products`.`id` = `product_translations`.`product_id`
    LEFT JOIN `categories` ON `products`.`category_id` = `categories`.`id`
    LEFT JOIN `category_translations` ON `categories`.`id` = `category_translations`.`category_id`
    LEFT JOIN `store_products_quantity` ON `products`.`id` = `store_products_quantity`.`product_id`
    WHERE (`orders`.`status` = 4
           AND `category_translations`.`locale` = 'en'
           AND date(`orders`.`schedule_date`) BETWEEN date(date_add(now(), INTERVAL -30 DAY)) AND date(date_add(now(), INTERVAL -1 DAY))
           AND `store_products_quantity`.`store_id` = 6
           AND (`product_translations`.`name` = 'بيبسى اكشن'
                OR `product_translations`.`name` = 'بيبسى اكشن عرض x6 ق'
                OR `product_translations`.`name` = 'بيبسي  مشروب مياه غازية -1لتر + 25 ملي زيادة'
                OR `product_translations`.`name` = 'بيبسي 2 لتر'
                OR `product_translations`.`name` = 'بيبسي تربو'
                OR `product_translations`.`name` = 'بيبسي دايت تربو'
                OR `product_translations`.`name` = 'بيبسي دايت كان'
                OR `product_translations`.`name` = 'بيبسي دايت لتر'
                OR `product_translations`.`name` = 'بيبسي عائلي - حجم أكبر'
                OR `product_translations`.`name` = 'بيبسي كان'
                OR `product_translations`.`name` = 'بيبسي كان جيب'
                OR `product_translations`.`name` = 'بيبسي لتر'
                OR `product_translations`.`name` = 'بيبسي ليمون كان'
                OR `product_translations`.`name` = 'بيبسي ليمون لتر'
                OR `product_translations`.`name` = 'بيبسي ميني تربو'
                OR `product_translations`.`name` = 'ميرندا برتقال تيربو'
                OR `product_translations`.`name` = 'ميرندا برتقال جيب'
                OR `product_translations`.`name` = 'ميرندا برتقال جيب عرض 3ق'
                OR `product_translations`.`name` = 'ميرندا برتقال عائلي'
                OR `product_translations`.`name` = 'ميرندا برتقال كان'
                OR `product_translations`.`name` = 'ميرندا برتقال لتر'
                OR `product_translations`.`name` = 'ميرندا برتقال ميني تيربو'
                OR `product_translations`.`name` = 'ميرندا تفاح اخضر زجاجه الهضبه'
                OR `product_translations`.`name` = 'ميرندا تفاح اخضر ميني تربو'
                OR `product_translations`.`name` = 'ميرندا تفاح تيربو'
                OR `product_translations`.`name` = 'ميرندا تفاح عائلي'
                OR `product_translations`.`name` = 'ميرندا تفاح كان'
                OR `product_translations`.`name` = 'ميرندا تفاح كان جيب'
                OR `product_translations`.`name` = 'ميرندا تفاح كان عرض 3ق'
                OR `product_translations`.`name` = 'ميرندا تفاح لتر'
                OR `product_translations`.`name` = 'سفن أب تربو'
                OR `product_translations`.`name` = 'سفن أب تربو - عرض x6 ق'
                OR `product_translations`.`name` = 'سفن أب عائلي'
                OR `product_translations`.`name` = 'سفن أب كان'
                OR `product_translations`.`name` = 'سفن أب كان جيب'
                OR `product_translations`.`name` = 'سفن أب كان جيب عرض 3 ق'
                OR `product_translations`.`name` = 'سفن أب لتر'
                OR `product_translations`.`name` = 'سفن أب لتر دايت'
                OR `product_translations`.`name` = 'سفن أب ميني تربو'
                OR `product_translations`.`name` = 'سفن اب دايت'
                OR `product_translations`.`name` = 'سفن اب دايت - كان'
                OR `product_translations`.`name` = 'سفن اب زجاجه الهضبه 350 مل'
                OR `product_translations`.`name` = 'Aquafina Gallon Water, 18.9 Liter'
                OR `product_translations`.`name` = 'Aquafina natural water'
                OR `product_translations`.`name` = 'Aquafina natural water - box'
                OR `product_translations`.`name` = 'Aquafina natural water - box-S'
                OR `product_translations`.`name` = 'سفن اب عائلي أكبر')
           AND `category_translations`.`name` = 'Soft drinks')
    GROUP BY `category_translations`.`name`,
             `categories`.`id`
    ORDER BY `count` DESC,
             `category_translations`.`name` ASC,
             `categories`.`id` ASC
)

SELECT alldrinks.name, alldrinks.id, SUM(alldrinks.count) AS all_count, SUM(justpepsi.count) AS pepsi_count, ( SUM(justpepsi.count) / SUM(alldrinks.count) ) as percentage
FROM alldrinks
LEFT JOIN justpepsi ON justpepsi.name=alldrinks.name AND justpepsi.id=alldrinks.id
GROUP BY alldrinks.name, alldrinks.id
3

thanks for your answer
it gives me this error
(conn=3738933) Unknown column 'Product

4

@smrasmy Well, I copied your queries, so then you need to correct your own queries. If you can't get it to work, then I don't think I'll be able to help.

1 Like
5

tried multiple times, the codes separately works,each on separate question
but combined as you send it gives me Unknown column 'Product
what could be the problem :thinking:

6

@smrasmy I don't know why it's complaining, but I have updated the SQL, so it doesn't include aliasing. Try that Compare and calculate ,product and and category
If that doesn't work, then I don't know, but you should ask in a forum dedicated to your database or stackoverflow.com, since it's unrelated to Metabase.

1 Like
7

thank you for your time, really appreciated

8

I just wanted to say that your Code worked perfectly
the first one
the issue was a double space in the code resulted from copy-paste
:upside_down_face:

1 Like