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

Open in new window

LVL 1
rgb192Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
EvilPostItConnect With a Mentor Commented:
Try douvle clicking on the error text, it should highlight the error line.
0
 
EvilPostItCommented:
sum(convert(decimal(18,2), replace(totalprice,',','')))
0
 
rgb192Author Commented:
i think that works on a smaller number 355,41
but wont work on a larger number 2.245,82
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rgb192Author Commented:
do I put that line on the top of the code

what about cost  (which is derived)
0
 
EvilPostItCommented:
ok try decimal(14,5)
0
 
EvilPostItCommented:
If you are using sum then you are going to need a group by statement as sum is an aggregate statement.
0
 
rgb192Author Commented:
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

Open in new window

0
 
EvilPostItCommented:
no the group by will need to be on the other columns. Not the sum.
0
 
rgb192Author Commented:
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

Open in new window

0
 
EvilPostItCommented:
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
0
 
rgb192Author Commented:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
0
 
rgb192Author Commented:
thanks found it.


comma error
0
All Courses

From novice to tech pro — start learning today.