How to get all the occurrence of each distinct value including its quantities in mysql?

user_8786892 0
Viewed 61

I have a SQL table that looks like this:

item_price | discount | quantity
  2000.00  | 360.00 | 02
  2000.00 | 360.00 | 01
  1589.00 | 250.00  | 01

How would I get all the occurrence of each item_prices but exclude discounts (include ones that multiple quantities)? for example: for item_price '2000.00', I will get 3 for its occurrence.

I have: 

select count(item_price) * quantity as occurrence from order_items group by 1;

But it only returns 2 for item_price '2000.00'

mysql-workbench mysql
1 Answers

Group by item_price, count by item_price and value of (qty*item_price) and average discount:

select
       item_price
     , count(*)          as occurrence 
     , sum(item_price * quantity) as value_of
     , avg(discount)              as avg_discount
from order_items 
group by
       item_price
;
user_20677530
Related