multi-calculation query

Product Table开发者_如何转开发

id      name       description       price

 1      AAA         AAAAAA           10.00
 2      BBB         BBBBBB           12.00
 3      CCC         CCCCCC           15.00
 4      DDD         DDDDDD            8.00
 5      EEE         EEEEEE           12.50

Sales Table

trackid     productid     affiliateid     paymentstatus     refundid

   1           2               1           COMPLETED         1
   2           2               0           DONE              null
   3           3               1           COMPLETED         null
   4           3               0           COMPLETED         null
   5           3               0           COMPLETED         null
   6           5               5           DONE              null
   7           5               0           COMPLETED         2
   8           5               2           COMPLETED         null
   9           2               0           DONE              null
  10           3               1           COMPLETED         3

For Sales table

• If there is no affiliate for a particular sale, then affiliateid would be 0 otherwise affiliateid (I have another table for user which lists vendors and affiliates)

• If a particular sales is been refunded (for any reason), then refundid would be set to some value, otherwise it would be null

• paymentstatus can have any 1 of 2 values, COMPLETED and DONE

Now I need query that list following data for each product in product table

 productid       name       totalsales      affsales        refunds

     1           AAA           0               0               0
     2           BBB           1               1               1
     3           CCC           4               2               1
     4           DDD           0               0               0 
     5           EEE           2               1               1

totalsales: sales with paymentstatus as "COMPLETED"

affsales: sales with paymentstatus as "COMPLETED" and affiliateid NOT EQUAL TO 0

refunds: sales with paymentstatus as "COMPLETED" and refundid NOT null

How can I form this particular query?

select 
      p.name,
      SalesSummary.productid,
      COALESCE( SalesSummary.TotalSales, 0 ) TotalSales 
      COALESCE( SalesSummary.AffSales, 0 ) AffSales 
      COALESCE( SalesSummary.Refunds, 0 ) Refunds 
   from
      product p
         left join
            ( select s.productid,
                    count(*) TotalSales 
                    sum( if( s.affiliateID > 0, 1, 0 )) affSales,
                    sum( if( ifnull( s.RefundID, 0 ) > 0, 1, 0 )) Refunds
                 from 
                    sales s
                 where
                    s.PaymentStatus = 'COMPLETED'
                 group by
                    s.productid
            ) SalesSummary
         on p.id = SalesSummary.productid