help with GROUP BY

[hessodreamy]

I've got a fairly complicated query on the go here, and I might have bitten off more than I can chew!
In short, i'm trying to get daily totals over a period for sales, alongside similar info from identically structured tables showing a reversal, or subtraction of any of the sales.

The sales are contained in the following tables:

tOrders(orderID, uts, discount, delivery, deliveryCost)
tOrderProds(purchaseOrder, productID, prodQty, salePrice, costPrice)
and a link table, tOrderPOs(orderID, purchaseOrder)

1 order can have many purchaseOrders, and any purchase Order can have many products.

Alongside this are similarly structured tables showing necessary subtractions to the orders, thereby retaining all the original info in the orders table:

reverseOrders(orderID, disocunt, delivery, deliveryCost)
reverseOrderProds(purchaseOrder, productID, prodQty, salePrice, costPrice)
reverseOrderPOs(orderID, purchaseOrder)

I've got the appropriate selects for each set of tables working, but when I try to left join them together, it only picks up 1 row from the reverseOrders, hence will sometimes miss it from the sum, depending on the order that the rows come up.

I think its probably a problem with my group by's. I can either get the reverseOrders added up ok OR get the orders added up. By getting all the orders I miss reversorders, and by getting all the reverses I get extra rows in my orders. Is this making sense?

I want it to list sums of :the price for sales, the cost for sales, the price for reversed sales, and the cost of reversed sales. All this for each day. Many thanks.

Anyway, here's the query:

select thisday, count(*)  as orders
     , sum(ordersale + delivery - discount) as total_price
, sum(ordercost) + sum(deliveryCost)  as total_cost
		, sum(revSalePrice + revDelivery-revDiscount) as total_revSale
	, sum(revCostPrice + revDeliveryCost) as total_revCost
  from (
       select from_unixtime(uts,'%Y-%m-%d') as thisday
		, o.delivery, discount
            , sum(op.costPrice*op.prodQty) as ordercost
		, sum(op.salePrice * op.prodQty) as ordersale
            , o.deliveryCost
		, revDiscount, revDelivery, revDeliveryCost, revProdQty
		, revSalePrice, revCostPrice
         from tOrders o
       inner
         join tOrderPOs pos
           on o.orderId
            = pos.orderID
       inner
         join tOrderProds op
           on pos.purchaseOrder
            = op.purchaseOrder

	LEFT JOIN (
            SELECT
            ro.orderID as revOrderId
		, ro.discount as revDiscount
		, ro.delivery as revDelivery
		, ro.deliveryCost as revDeliveryCost
		, ro.reverseOrder
		, rop.purchaseOrder as revPONumber
		, rpos.reversePO
            , rop.productId as revProductId
		, sum(rop.prodQty) as revProdQty
		, sum(rop.salePrice) as revSalePrice
		, sum(rop.costPrice) as revCostPrice
            FROM reverseOrders ro
            INNER JOIN reverseOrderPOs rpos ON ro.orderId = rpos.orderId
            INNER JOIN reverseOrderProds rop ON rpos.purchaseOrder = rop.purchaseOrder
            LEFT JOIN tOrders o ON ro.orderId = o.orderId
	        where from_unixtime(uts,'%Y-%m')= '2005-08'
		group by ro.orderID, ro.deliveryCost
		) as reverse
	ON op.purchaseOrder = revPONumber AND op.productID = revProductId


        where from_unixtime(uts,'%Y-%m')= '2005-08'

       group
           by o.orderid
            , o.deliveryCost
       ) as ordertotals
	group by thisday

 

[hessodreamy]

OK i've fixed the query now and, on the off chance that anyone is stuck trying to do EXACTLY the same thing, here it is:

select thisday, count(*)  as orders
     	, sum(ordersale) as tot_sale
, sum(ordercost)  as tot_cost
	, sum(revSale) as tot_revSale
	, sum(revCost) as tot_revCost
	, uts
	, sum(revorders) as revorders
  from (


select from_unixtime(uts,'%a %d-%m-%Y') as thisday
            , uts
          , o.orderid
,o.vatRate
          , round(sum(op.salePrice * op.prodQty) + delivery - discount,2) as ordersale
             , round(sum(op.costPrice*op.prodQty) + deliveryCost,2) as ordercost
, round((revOrderSale + revDelivery - revDiscount)/(1+vatRate),2) as revSale
, round((revOrderCost -  revDeliveryCost)/(1+vatRate),2) as revCost
, revorders
          from tOrders o
        inner
          join tOrderPOs pos
            on o.orderId
             = pos.orderID
        inner
          join tOrderProds op
            on pos.purchaseOrder
             = op.purchaseOrder
LEFT JOIN (
select count(distinct ro.orderId) as revorders, ro.orderid as revOrderId, ro.delivery as revDelivery, ro.discount as revDiscount
             , sum(rop.costPrice) as revOrdercost
          , sum(rop.salePrice) as revOrdersale
             , ro.deliveryCost as revDeliveryCost
          from reverseOrders ro
        inner
          join tOrderPOs rpos
            on ro.orderId
             = rpos.orderID
        inner
          join reverseOrderProds rop
            on rpos.purchaseOrder
             = rop.purchaseOrder
left join tOrders o on ro.orderId = o.orderId
         WHERE uts > 1129935600 AND uts <= 1130022000 
        group
            by ro.orderid
             , ro.deliveryCost

) as reverses ON o.orderId = revOrderId
        WHERE uts > 1129935600 AND uts <= 1130022000 
        group
            by o.orderid
             , o.deliveryCost
order by uts
   ) as ordertotals
	group by thisday
	order by uts