Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dmh188
Starting Member
37 Posts |
 Posted - 2011-07-13 : 09:48:02
|
| Hey guys, i know there is a lot of posts about this topic but i can not find one that works for me. I need to get the count of 2 fields and then divide and multiply to get a percent. Here is what i have:declare @startdate datetime, @enddate datetimeselect count( distinct x.order_no) as Total_Shipped, count(v.order_no) as Free_Shipped,CAST((select count(*) from vw_free_ship ) as FLOAT)/(select count(distinct order_no) from x_invoic where item_code= 'sh')*100.0 as percentage, datepart(year,x.dw_update_date) as 'Year', Datepart(mm, x.dw_update_date)as 'Month' from x_invoic xleft outer join vw_free_ship von x.dw_update_date=v.dw_update_datewhere x.item_code ='sh'and x.status = 9and x.dw_update_date >= '01/01/2010'and x.dw_update_date <= '07/01/2010'group by datepart(year,x.dw_update_date), datepart(mm, x.dw_update_date)The only problem with this is it returns this result:Total_Shipped Free_Shipped percentage Year Month11635 2644 19.1696597148376 2010 111537 2659 19.1696597148376 2010 214141 3138 19.1696597148376 2010 313100 3095 19.1696597148376 2010 412045 2718 19.1696597148376 2010 514965 3297 19.1696597148376 2010 6Which as you can see it returns the same percent for all months. Any idea on how to get this working correctly. any help would be much appreciated |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-07-13 : 19:21:27
|
| Try changing:CAST((select count(*) from vw_free_ship ) as FLOAT)/(select count(distinct order_no) from x_invoic where item_code= 'sh')*100.0 as percentage,To:(CAST(count(*) as FLOAT)/count(distinct order_no))*100.0 as percentage,Your sub query is bypassing your group, hence the problem.Pete |
 |
|
|
|
|
|
|
|
get sum of 2 divided counts, 2 tabl, group by mm