rgb192
asked on
sum columns in query
this query works
but I want sum of subtotal, total(derived), cost(derived)
but I want sum of subtotal, total(derived), cost(derived)
select dateordered, company, orderid, billfullname, email, oitemid, cost1, cost2, cost3, productid, ptitle, etitle, iitemid, status, sku, quantity, cost,
case when iitemid = minitemid then debit else 0 end total, case when iitemid=minitemid then subtotal else 0 end subtotal from (
select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid
,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle
,e.title as etitle
,s.status
,i.sku,i.quantity,i.itemid as iitemid
,CASE sku
WHEN p.internalsku1 THEN p.cost1*i.quantity
WHEN p.internalsku2 THEN p.cost2*i.quantity
WHEN p.internalsku3 THEN p.cost3*i.quantity
ELSE 0 END as cost
,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal
,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and 'confirmation'!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit
,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid
from orders o
INNER JOIN orderitems i on i.orderid = o.orderid
INNER JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where (o.dateordered between '11/01/2010' and '11/09/2010')
AND o.orderid In (select orderid from orderitems i,Products P Where i.productid = p.productid and P.title LIKE '%%')
and o.statusid in (10,90,110,260)
and ( (company='company1'and o.itemid>0) or (company='company2'and o.itemid>0) or o.itemid=0 )
) q
In the outer select, would it not work if you just slap on a sum() for everything you want to sum up, and add a group by for everything else?
ASKER
just adding sum(subtotal)
Msg 8120, Level 16, State 1, Line 1
Column 'q.dateordered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'q.dateordered' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
select dateordered, company, orderid, billfullname, email, oitemid, cost1, cost2, cost3, productid, ptitle, etitle, iitemid, status, sku, quantity, cost,
case when iitemid = minitemid then debit else 0 end total, case when iitemid=minitemid then subtotal else 0 end subtotal
, sum(subtotal)
from (
select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid
,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle
,e.title as etitle
,s.status
,i.sku,i.quantity,i.itemid as iitemid
,CASE sku
WHEN p.internalsku1 THEN p.cost1*i.quantity
WHEN p.internalsku2 THEN p.cost2*i.quantity
WHEN p.internalsku3 THEN p.cost3*i.quantity
ELSE 0 END as cost
,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal
,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and 'confirmation'!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit
,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid
from orders o
INNER JOIN orderitems i on i.orderid = o.orderid
INNER JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where (o.dateordered between '11/01/2010' and '11/09/2010')
AND o.orderid In (select orderid from orderitems i,Products P Where i.productid = p.productid and P.title LIKE '%%')
and o.statusid in (10,90,110,260)
and ( (company='company1'and o.itemid>0) or (company='company2'and o.itemid>0) or o.itemid=0 )
) q
You only did sum. Where is the group by clause at the bottom? You have to put everything else thats not aggregated in the group by clause.
GROUP BY DATEORDERED, COMPANY, ORDERID, etc.
GROUP BY DATEORDERED, COMPANY, ORDERID, etc.
ASKER
old errors:
invalid column 'total'
error by 'group by'
Msg 8120, Level 16, State 1, Line 3
Column 'q.minitemid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'q.debit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
so I commented subtotal,total,q
current error:
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'group'.
invalid column 'total'
error by 'group by'
Msg 8120, Level 16, State 1, Line 3
Column 'q.minitemid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 3
Column 'q.debit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
so I commented subtotal,total,q
current error:
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'group'.
select dateordered, company, orderid, billfullname, email, oitemid, cost1, cost2, cost3, productid, ptitle, etitle, iitemid, status, sku, quantity, cost,
case when iitemid = minitemid then debit else 0 end total, case when iitemid=minitemid then subtotal else 0 end subtotal
--, sum(subtotal)
from (
select o.dateordered,o.company,o.orderid,RTRIM ( LTRIM( o.billfirstname + ' ' + o.billlastname ) ) billfullname,o.email,o.itemid as oitemid
,p.cost1,p.cost2,p.cost3,p.productid,p.title as ptitle
,e.title as etitle
,s.status
,i.sku,i.quantity,i.itemid as iitemid
,CASE sku
WHEN p.internalsku1 THEN p.cost1*i.quantity
WHEN p.internalsku2 THEN p.cost2*i.quantity
WHEN p.internalsku3 THEN p.cost3*i.quantity
ELSE 0 END as cost
,(select sum(subtotal) from orderitems where orderid=o.orderid)as subtotal
,(select SUM(debit) from payments where orderid=o.orderid and ( (type='Paypal' and 'confirmation'!='' and resultcode!='Denied' and resultcode!='unconfirmed') or (type!='Paypal') ) )as debit
,(select min(itemid) from orderitems where orderid=o.orderid)as minitemid
from orders o
INNER JOIN orderitems i on i.orderid = o.orderid
INNER JOIN products p on i.productid = p.productid
left join ebaydata e on o.orderid = e.orderid
left join sstatuses s on o.statusid=s.statusid
where (o.dateordered between '11/01/2010' and '11/09/2010')
AND o.orderid In (select orderid from orderitems i,Products P Where i.productid = p.productid and P.title LIKE '%%')
and o.statusid in (10,90,110,260)
and ( (company='company1'and o.itemid>0) or (company='company2'and o.itemid>0) or o.itemid=0 )
)
--q
group by dateordered, company, orderid, billfullname, email, oitemid, cost1, cost2, cost3, productid, ptitle, etitle, iitemid, status, sku, quantity, cost
--,total
, subtotal
As the error explains, you can't use something in the select list if its not either aggregated, such as with sum(field), or its not put into the group by clause.
so, put minitemid and debit in the group by clause.
so, put minitemid and debit in the group by clause.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're going to need to leave the q in there. thats the alias for your temporary table.
ASKER
I changed the query to work. And I think
34104486
and
34103864
led to correct answer
34104486
and
34103864
led to correct answer
ASKER
thanks