CODEWITHSUNDEEP
sql
Sackling
Sackling

Reputation: 1820

select count of sold products with 2 attributes on different rows

I am trying to generate a report of every product sold of SKUABC in size 34 with inseam 33 (it is available in 33 and 31 inseam). Table - orders_products

Table - Orders:

+-----------+------------------------+--+
| Orders_id |     date_purchased     |  |
+-----------+------------------------+--+
|    46198  | 2020-10-18 19:43:25    |  |
|     46199 | 2020-10-19 19:43:25    |  |
|     46200 | 2020-10-22 19:43:25    |  |
+-----------+------------------------+--+

Table - orders_products

+--------------------+-----------+-------------+----------------+--+
| orders_products_id | Orders_id | products_id | products_mode | QTY
+--------------------+-----------+-------------+----------------+--+
|           42154907 |     46198 |         878 | SKUABC         |1  |
|           42154908 |     46198 |         878 | SKUABC         |1  |
|           42154909 |     46198 |         282 | DIFFSKU        |1  |
+--------------------+-----------+-------------+----------------+--+

Table - Orders_products_attributes (showing order_id 46198 only):

+------------------------------+-----------+--------------------+-----------------+-----------------------+--+
| orders_products_attribute_id | orders_id | orders_products_id | Product options | Product_options_value |  |
+------------------------------+-----------+--------------------+-----------------+-----------------------+--+
|                      167618  |     46198 |           42155189 | Color           | Green                 |  |
|                       167619 |     46198 |           42155189 | Inseam          | 33                    |  |
|                       167620 |     46198 |           42155189 | Size            | 34                    |  |
+------------------------------+-----------+--------------------+-----------------+-----------------------+--+

my sql so far:

SELECT  distinct o.orders_id, op.products_model, opa.products_options_values, sum(op.products_quantity)
FROM orders o  
LEFT JOIN orders_products op 
    ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa
    on op.orders_id = opa.orders_id
WHERE op.products_model in ('SKUABC')
and  opa.`products_options_values` in ('36')
and o.date_purchased > '2020-10-13'

If I add in :

and  opa.`products_options_values` in ('31')

it returns no results, the reason being because the inseam and size rows are separate. and the problem with the above code is that it is combining any orders/ordered products where the inseam is both 33 or 31 but I want it to be separate.

My desired out would be

+--------+------------+------------+-------------------+
| model  | attribute1 | attribute2 | quantity sold sum |
+--------+------------+------------+-------------------+
| ABCSKU |         34 |         33 |               120 |
+--------+------------+------------+-------------------+

Upvotes: 0

Views: 31

Answers (1)

T. Peter
T. Peter

Reputation: 887

Here is a fun solution: select two products_options_values and label those in different name then everything will be easy

SELECT  distinct o.orders_id, op.products_model, opa.products_options_values 
AS Inseem,opa2.products_options_values AS Size, sum(op.products_quantity)
FROM orders o  
LEFT JOIN orders_products op 
ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa
ON op.orders_id = opa.orders_id
LEFT JOIN orders_products_attributes opa2
ON op.orders_id = opa.orders_id
--your condition below

then just use opa for inseem and opa2 for size.It is stupid but work.You can even make the rows data null by adding some condition with Product option column for easier insert later.

Upvotes: 1

Related Questions