sql server - SQL nvarchar is invalid for sum operator -
i learning sql , have table looks this:
id name payd note 1 john 5.00 r:8days;u:5$ 2 adam 5.00 r:8days; 3 john 10.00 r:8days; 4 john 10.00 r:8days; 5 adam 15.00 r:30days; i want make this:
id name usage 5.00 10.00 15.00 sum 1 john 5 5.00 20.00 0 25.00 2 adam 5.00 0 15.00 20.00 i want check note column if there 'u:5$' in , add 5 customer has 'u:5$' in note column, if doesnt doesnt add anything.
my code looks this:
;with cte ( select customer, paydamount, paydamount payd, note usage t1 ) select customer, usage ,[4.00] = isnull([4.00],0) ,[5.00] = isnull([5.00],0) ,[9.00] = isnull([9.00],0) ,[10.00] = isnull([10.00],0) ,[15.00] = isnull([15.00],0) ,[18.00] = isnull([18.00],0) ,[20.00] = isnull([20.00],0) ,[25.00] = isnull([25.00],0) ,[50.00] = isnull([50.00],0) ,[payd] =isnull([4.00],0) + isnull([5.00],0) + isnull([9.00],0) + isnull([10.00],0) + isnull([15.00],0) + isnull([18.00],0) + isnull([20.00],0) + isnull([25.00],0) + isnull([50.00],0) cte pivot ( sum(paydamount) payd in ([4.00],[5.00],[9.00], [10.00], [15.00],[18.00], [20.00], [25.00], [50.00]))pvt order customer;
i not sure full output represents. first 3 columns easy using conditional aggregation:
select row_number() on (order (select null)) id, name, max(case when note '%u:5$' 5 end) usage, sum(case when payd = 5.00 payd else 0 end) [5.00], sum(case when payd = 10.00 payd else 0 end) [10.00], sum(case when payd = 15.00 payd else 0 end) [15.00], sum(payd) total cte group name; note columns 5, 10, , 15 sort of assume value in payd decimal/numeric type. equality comparisons on floats not recommended.
Comments
Post a Comment