asked on

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
``````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), replace(totalprice,',','')))

ASKER

i think that works on a smaller number 355,41
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)
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'.
``````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'.
``````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), replace(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 ))

)
GROUP BY 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

ASKER

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
ASKER CERTIFIED SOLUTION
EvilPostIt

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