May 10, 2010 at 7:59 am
Maybe someone out there can help on this. I have a pretty simple group by State query that works perfectly except for the fourth column (highlighted) does not group correctly. It does the calculation for the first row perfectly but carries that same result to the other 49 states which is not correct, but the other columns group just perfectly. Any ideas on this one? Thanks.:-)
SELECT T3.state AS [State],
SUM(T1.Applications) As Total,
SUM(T1.UnitsSold) as TotalSold,
convert(decimal(10,2),convert(decimal(10,2),SUM(T1.UnitsSold))/convert(decimal(10,2),SUM(T1.Applications)) * 100) as [Acceptance Rate %],
SUM(T1.Revenue) as Revenue
FROM TABLE1 T1 WITH(NOLOCK)
INNER JOIN TABLE2 T2 WITH(NOLOCK) ON T1.VendorID=T2.VendorID
INNER JOIN TABLE3 T3 ON T1.VendorID = T3.VendorID
AND T1.VendoriD = 88052
GROUP BY T3.STATE
ORDER BY T3.state
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
May 10, 2010 at 8:42 am
Hi,
Try splitting the work using a CTE:
; with cte as (
SELECT T3.state AS [State],
SUM(T1.Applications) As Total,
SUM(T1.UnitsSold) as TotalSold,
SUM(T1.Revenue) as Revenue,
T1.VendorId
FROM TABLE1 T1 WITH(NOLOCK)
INNER JOIN TABLE2 T2 WITH(NOLOCK) ON T1.VendorID=T2.VendorID
INNER JOIN TABLE3 T3 ON T1.VendorID = T3.VendorID
GROUP BY T3.STATE, T1.VendorId
)
select *
, cast(cast(TotalSold as decimal (10,2))/cast(Total as decimal(10,2)) as decimal(10,2)) as [Acceptance Rate %]
from cte
where cte.VendoriD = 88052
ORDER BY cte.state
May 10, 2010 at 9:01 am
Nope, stills shows the same acceptance rate for all 50 states. It is only correct for the first state. (AL).:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
May 10, 2010 at 9:09 am
Please provide some insert ready test data and expected results for tested code.
-- Cory
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply