Solved

sum of cost, sum of totalprice

Posted on 2010-11-11
12
302 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

728 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