CODEWITHSUNDEEP
sqlsql-server
vidooo
vidooo

Reputation: 135

SQL nvarchar is invalid for sum operator

I am learning SQL and I have table that looks like 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 to make something like 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 to check that note column if there is 'U:5$' in it and then add a 5 to that customer that has 'U:5$' in note column, if it doesnt it doesnt add anything.

My code looks like this:

;with cte as (
select Customer, PaydAmount, PaydAmount as Payd, Note as Usage
from 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)
    from cte
    pivot (
        sum(PaydAmount) for Payd in ([4.00],[5.00],[9.00], [10.00], [15.00],[18.00], [20.00], [25.00], [50.00]))pvt
        order by Customer;

Upvotes: 0

Views: 230

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I am not sure what your full output represents. But the first three columns are easy to get using conditional aggregation:

select row_number() over (order by (select null)) as id,
       name,
       max(case when note like '%U:5$' then 5 end) as usage,
       sum(case when payd = 5.00 then payd else 0 end) as [5.00],
       sum(case when payd = 10.00 then payd else 0 end) as [10.00],
       sum(case when payd = 15.00 then payd else 0 end) as [15.00],
       sum(payd) as total
from cte
group by name;

Note that the columns for 5, 10, and 15 sort of assume that the value in payd is a decimal/numeric type. Equality comparisons on floats are not recommended.

Upvotes: 2

Related Questions