Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sum of cost, sum of totalprice

Posted on 2010-11-11
12
Medium Priority
?
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
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…

688 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