sum of cost, sum of totalprice
I want the sum of totalprice (varchar)
and cost (derived from decimal (18,2)
these are totalprice (varchar)
some are
,
and others are
.
and others have both
. ,
93.06
93.01
924,95
355.24
355,96
355,41
2.245,82
2.074,95
2.034,95
2,579.70
1.284,81
1.284,81
1.275,81
and cost (derived from decimal (18,2)
these are totalprice (varchar)
some are
,
and others are
.
and others have both
. ,
93.06
93.01
924,95
355.24
355,96
355,41
2.245,82
2.074,95
2.034,95
2,579.70
1.284,81
1.284,81
1.275,81
select
--sum(convert(tofloat, totalprice))
,orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,p.internalsku2,p.internalsku3,p.cost1,p.cost2,p.cost3
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end as cost from a
left join products p on a.sku=p.internalsku1 or a.sku=p.internalsku2 or a.sku=p.internalsku3 where (cast( orderdate as datetime ) between convert ( datetime, '10/01/2010', 101 ) and convert ( datetime, '11/02/2010', 101 )) order by orderdate desc
EvilPostIt
sum(convert(decimal(18,2),
ASKER
i think that works on a smaller number 355,41
but wont work on a larger number 2.245,82
but wont work on a larger number 2.245,82
ASKER
do I put that line on the top of the code
what about cost (which is derived)
what about cost (which is derived)
ok try decimal(14,5)
If you are using sum then you are going to need a group by statement as sum is an aggregate statement.
ASKER
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'group'.
Incorrect syntax near the keyword 'group'.
select sum(convert(decimal(14,5), replace(totalprice,',',''))) (
select
orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,p.internalsku2,p.internalsku3,p.cost1,p.cost2,p.cost3
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end as cost from a
left join products p on a.sku=p.internalsku1 or a.sku=p.internalsku2 or a.sku=p.internalsku3 where (cast( orderdate as datetime ) between convert ( datetime, '10/01/2010', 101 ) and convert ( datetime, '11/02/2010', 101 ))
)group by sum(convert(decimal(14,5), replace(totalprice,',','')))
--order by orderdate
no the group by will need to be on the other columns. Not the sum.
ASKER
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'group'.
Incorrect syntax near the keyword 'group'.
select sum(convert(decimal(14,5), replace(totalprice,',',''))) (
select
orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,p.internalsku2,p.internalsku3,p.cost1,p.cost2,p.cost3
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end as cost from a
left join products p on a.sku=p.internalsku1 or a.sku=p.internalsku2 or a.sku=p.internalsku3 where (cast( orderdate as datetime ) between convert ( datetime, '10/01/2010', 101 ) and convert ( datetime, '11/02/2010', 101 ))
)group orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,p.internalsku2,p.internalsku3,p.cost1,p.cost2,p.cost3
What does this return.
select
sum(convert(decimal(14,5),
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end as cost from a
left join products p on a.sku=p.internalsku1 or a.sku=p.internalsku2 or a.sku=p.internalsku3 where (cast( orderdate as datetime ) between convert ( datetime, '10/01/2010', 101 ) and convert ( datetime, '11/02/2010', 101 ))
)
GROUP BY orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end
select
sum(convert(decimal(14,5),
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end as cost from a
left join products p on a.sku=p.internalsku1 or a.sku=p.internalsku2 or a.sku=p.internalsku3 where (cast( orderdate as datetime ) between convert ( datetime, '10/01/2010', 101 ) and convert ( datetime, '11/02/2010', 101 ))
)
GROUP BY orderdate,saleschannel, fulfilmentchannel, orderid,productdetails, qty, sku, contactbuyer, shippingservice, status, refundinfo, totalprice,p.internalsku1,
, case a.sku
when p.internalsku1 then p.cost1*qty
when p.internalsku2 then p.cost2*qty
when p.internalsku3 then p.cost3*qty
end
ASKER
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Incorrect syntax near ','.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks found it.
comma error
comma error