1

I have a order table with the following information

Order ID, Product ID, Quantity ordered

OID PID Qty
1   10  1
1   20  2
2   10  2
2   40  4
3   50  1
3   20  3
4   30  1
4   90  2
4   90  5
5   10  2
5   20  2
5   70  5
5   60  1
6   80  2

If I run the following query 

select `Qty`, count(`Qty`) 
from `table`
group by `Qty`

I get the distribution of quantities in the table, which is 

Qty count(`Qty`)
1   4
2   6 
3   1
4   1
5   2

I want to find the distribution of quantity at order_line_item level too

That is how many orders which have one line item, had items with 1 quantity, 2 quantity and so one, something like 

Count(Order_line_item)    Qty        Count(Qty)
1                         2          1
2                         1          2
2                         2          2
2                         3          1
2                         4          1
3                         1          1
3                         2          1
3                         5          1
4                         1          1
4                         2          2
4                         5          1

What modification should i make in the above query to achieve this

sakura01
  • 13
  • 4

1 Answers1

0

Try this query

SELECT  count_order_line_items, `Qty`, count(*)
FROM (
   SELECT count(*) over (partition by `OID`) as count_order_line_items,
         `Qty`
   FROM Table1
) x
GROUP BY count_order_line_items, `Qty`

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=07dfb27a7d434eca1f0b9641aadd53c8

If your mysql version is less than 8 then try this one

SELECT count_order_line_items, `Qty`, count(*)
FROM Table1 t1
JOIN (
   SELECT `OID`, count(*) as count_order_line_items
   FROM Table1
   GROUP BY `OID`
) t2 ON t1.`OID` = t2.`OID`
GROUP BY count_order_line_items, `Qty`

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=28c291a4693f31029a103f5c41a97d77

krokodilko
  • 35,300
  • 7
  • 55
  • 79
  • Thank you for your answer @krokodilko , But when i run this for my table, I am getting the error --------- You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by `OID`) as count_order_line_items, `Qty` F' at line 3 ------------ what might be causing this since it is working in fiddle – sakura01 Jun 11 '20 at 06:44
  • @sakura01 Your version of mysql is pre 8.0 – Strawberry Jun 11 '20 at 07:13
  • ok, I wan't aware about that, Thank you, what can be done in such a case @Strawberry – sakura01 Jun 11 '20 at 07:20
  • I've added a new query for versions less than 8 to my answer – krokodilko Jun 11 '20 at 07:45
  • Thank you @krokodilko this works perfectly for me! :) god bless you ! – sakura01 Jun 11 '20 at 08:36