Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sum of cost, sum of totalprice

Posted on 2010-11-11
12
Medium Priority
?
309 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:rgb192
  • 6
  • 6
12 Comments
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34111356
sum(convert(decimal(18,2), replace(totalprice,',','')))
0
 

Author Comment

by:rgb192
ID: 34111507
i think that works on a smaller number 355,41
but wont work on a larger number 2.245,82
0
 

Author Comment

by:rgb192
ID: 34111515
do I put that line on the top of the code

what about cost  (which is derived)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34111520
ok try decimal(14,5)
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34111553
If you are using sum then you are going to need a group by statement as sum is an aggregate statement.
0
 

Author Comment

by:rgb192
ID: 34111594
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34111603
no the group by will need to be on the other columns. Not the sum.
0
 

Author Comment

by:rgb192
ID: 34111621
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
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34111665
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
 

Author Comment

by:rgb192
ID: 34111823
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 34111992
Try douvle clicking on the error text, it should highlight the error line.
0
 

Author Closing Comment

by:rgb192
ID: 34220138
thanks found it.


comma error
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question