SUM query - help needed 1

[hessodreamy]

I'm trying to do a query to get sales totals. Here's the definitions:

CREATE TABLE `torders` (
  `orderID` int(11) unsigned NOT NULL auto_increment,
  `uts` int(11) NOT NULL default '0',
  `subTotal` float NOT NULL default '0',
  `delivery` float NOT NULL default '0',
  `deliveryCost` float NOT NULL default '0',
) ;

CREATE TABLE `torderpos` (
  `purchaseOrder` int(11) unsigned NOT NULL auto_increment,
  `orderID` int(11) unsigned NOT NULL default '0',
)

CREATE TABLE `torderprods` (
  `purchaseOrder` int(11) unsigned NOT NULL default '0',
  `productID` int(11) unsigned NOT NULL default '0',
  `prodQty` int(11) NOT NULL default '0',
  `salePrice` float NOT NULL default '0',
  `costPrice` float NOT NULL default '0',
)

tOrders has the delivery prices, and tOrderProds has item costs, but naturally when I do a join between the tables, the delivery costs for the whole order appear in every row, so when I do a sum, I end up with the wrong figures. Here's the query I've been using:

SELECT sum(costPrice*prodQty)+sum(deliveryCost) as cost
from tOrders o 
INNER JOIN tOrderPOs pos ON o.orderId=pos.orderID 
INNER JOIN tOrderProds op ON pos.purchaseOrder=op.purchaseOrder
WHERE from_unixtime(uts,'%Y-%m-%d')='2005-09-08'
GROUP BY o.orderid ORDER BY uts;

Any ideas how I stop the query from adding delivery charges twice?

 

[r937]

your GROUP BY is missing a couple of columns, and one of them should be deliverycost, which you don't want to SUM for each group, do you

select o.orderid
     , o.uts
     , sum(op.costPrice*op.prodQty)
       + o.deliveryCost as cost
  from tOrders o 
inner 
  join tOrderPOs pos 
    on o.orderId
     = pos.orderID 
inner 
  join tOrderProds op 
    on pos.purchaseOrder
     = op.purchaseOrder
 where from_unixtime(uts,'%Y-%m-%d')
     = '2005-09-08'
group 
    by o.orderid 
     , o.uts
     , o.deliveryCost
order 
    by o.uts

r937.com | rudy.ca

 

[hessodreamy]

ah. that's works great, that does.
But now how do I modify the query to return a total of the cost column?

 

[r937]

why a total of the cost column? there is only one cost per order

r937.com | rudy.ca

 

[hessodreamy]

to get a total of costs for a given day

 

[r937]

oh, i see

okay, just add it to the SELECT, it's already in the GROUP BY

r937.com | rudy.ca

 

[hessodreamy]

Not sure I get you.
The cost for an order is given by

sum(op.costPrice*op.prodQty) + o.deliveryCost as cost

But what I really want is the sum of all the rows for this field. But I can't do

sum(sum(op.costPrice*op.prodQty)
       + o.deliveryCost) as cost

I've tried doing

group by 
from_unixtime(uts,'%Y-%m-%d') 
, o.deliveryCost

but that returns 3 rows with the wrong figures in.

 

[r937]

in your first post, you have GROUP BY o.orderid

am i to understand correctly that you now do not want totals by order?

r937.com | rudy.ca

 

[hessodreamy]

that's right. Change of plan. I just want a single row returned, with the sum total of (costPrice *prodQty) from all matches on tOrderProds, added to the sum of all deliveryCosts from tOrders.

I usually use PHP to iterate though query results and do a lot of the work so that I needn't do a better query. I'm just trying to up my sql game and do better queries.

 

[r937]

select count(*)   as orders
     , sum(ordercost)
       + sum(deliveryCost) 
                  as total_cost
  from (
       select o.orderid 
            , sum(op.costPrice
                 *op.prodQty) 
                      as ordercost
            , o.deliveryCost  
         from tOrders o 
       inner 
         join tOrderPOs pos 
           on o.orderId
            = pos.orderID 
       inner 
         join tOrderProds op 
           on pos.purchaseOrder
            = op.purchaseOrder
        where from_unixtime(uts
               ,'%Y-%m-%d')
            = '2005-09-08'
       group 
           by o.orderid 
            , o.deliveryCost
       ) as ordertotals

r937.com | rudy.ca

 

[hessodreamy]

as. a subquery. thats the bunny. Cheers.

 

[hessodreamy]

Hi. Just thought i'd let you know that since being pointed in the right direction on this one, things have really fallen into place and I'm running wild with group by and subqueries.

Who would have thought that forums were good for more than getting other people to do the thinking for you?