MySQL JOIN Tables with referenced Columns -

i try most(30) ordered products db.

in table order have column type values (1 or -1). 1 valid user order, -1 cancellation order(in case order has reference id user order)

reference_id id of order (in same order table)| 1 row referenced row.

order table:

id   |   reference_id  | type ----------------------------------  1    |                 | 1 ----------------------------------  2    |                 | 1 ----------------------------------  3    |   1             | -1 ---------------------------------- 

products table:

id   |   order_id   |   quantity ----------------------------------    |   1          |   4 ---------------------------------- b    |   2          |   7 ----------------------------------    |   3          |   2 ---------------------------------- 

mysql query:

select *, sum(product.quantity) quantity, count(*) score  product left join order on ( product.order_id=order.id )  (..?..) group product.id order score desc limit 30; 

this select, sum , count products in orders.

but:

what looking is:

if(order.type < 0 ){     product.quantity in minus(-)     (quantity of product in referenced order minus this.product.quantity) } 

how in sql statement? tried somany things without success

as per comments requested example: result should list of ordered products grouped product.id ordered products:

product-id   |   quantity -------------------------------------------            |   2 (in 2 orders) ------------------------------------------- b            |   7 (in 1 order) ------------------------------------------- c            |   12 (in 3 orders) ------------------------------------------- ...etc. 

many thanks

this count orders , give sum 9

this query works here http://www.sqlfiddle.com/#!2/9c3a1/6/0

 select sum(orders.type * products.quantity) quant,         count(products.id) count,        products.id    orders         inner join products                 on orders.id = products.order_id  

if want count of 2 (2 products have been ordered)

select sum(orders.type * products.quantity) quant,         count(distinct products.id) count,        products.id    orders         inner join products                 on orders.id = products.order_id  

count 1 each here , sum 7 , 2 (as per latest edit in question)

select products.id, sum(orders.type * products.quantity) quant,         count(distinct orders.id) count   orders         inner join products                 on orders.id = products.order_id  group products.id 

this gives have asked