Operand data type varchar is invalid for sum operator

Question!

I try this query

Select 
S.Name,S.No,
SUM(Case when s.Model='Cultus' then total else 0 end) as Cultus,
SUM(Case when s.Model ='vigo' then total else 0 end) as vigo,
SUM(total) total_v ,
 s.MA,MAX(S.Speed) Speed
 from (
Select
 RVU.Name,RVU.No,VV.Model,count(VV.Model) as total, RVU.MA as MA,RVU.Speed 
from 
 VV 
inner join RVU
on VV.MID=RVU.ID 
inner join RU on RVU.RID= RU.RID
    WHERE 
RU.StartDate>= '2016-04-01 00:00:00' and
RU.EndDate<='2016-04-30 23:59:59'   and
RU.Region= 'Paris'
and RVU.No= '651' AND Model <> ''
Group By RVU.Name,RVU.RegNo,VV.Model,RVU.MA,RVU.Speed ) S
GROUP BY
s.RegNo,s.Name,S.MA

Ouput

Name    No  Cultus  vigo    total_v   MA       Speed
David   651    2    0        2         1048      124
David   651    3    0        3         597      345
David   651    1    0        1         606     101
David   651    3    2        5         992     110

when i try this

Sum(MA) MA,

THIS SHOW ERROR

Operand data type varchar is invalid for sum operator.

I want result like this

Name    No     Cultus   vigo    total_v   MA    Speed
David   651    9         2       11       1799    345

UPDATE

when i try this

SUM(CASE ISNULL(MA,'') WHEN '' THEN 0 ELSE CAST(MA AS INT) END),

and also this

sum(cast (MA as int))

then output is

 Name   No  Cultus  vigo    total_v     MA     Speed
David   651    2    0        2          4192     124
David   651    3    0        3         2388      345
David   651    1    0        1         2424      101
David   651    3    2        5         4960      110

updated 2:

when i execute only this part this show multiple records with same data

 Select
     RVU.Name,RVU.No,VV.Model,count(VV.Model) as total, RVU.MA as MA,RVU.Speed 
    from 
     VV 
    inner join RVU
    on VV.MID=RVU.ID 
    inner join RU on RVU.RID= RU.RID
        WHERE 
    RU.StartDate>= '2016-04-01 00:00:00' and
    RU.EndDate<='2016-04-30 23:59:59'   and
    RU.Region= 'Paris'
    and RVU.No= '651' AND Model <> ''
    Group By RVU.Name,RVU.RegNo,VV.Model,RVU.MA,RVU.Speed

looks like value totally change



Answers

Seems that one of your MA column value is type of varchar, please check your data

If your data type varchar then cast to INT:

SUM(CASE ISNULL(MA,'') WHEN '' THEN 0 ELSE CAST(MA AS INT) END)

DECLARE @tblTest as Table(
    Name VARCHAR(10),
    No INT,
    Cultus  INT,
    vigo INT,
    total_v INt,
    MA VARCHAR(5),
    Speed INT
)

INSERT INTO @tblTest VALUES
('David',651,2,0,2,1048,124)
,('David',651,3,0,3,597,345)
,('David',651,1,0,1,606,101)
,('David',651,3,2,5,992,110)

SELECT
    Name,[No],SUM(Cultus),
    SUM(vigo) AS vigo ,SUM(total_v) AS total_v,
    SUM(CASE ISNULL(MA,'') WHEN '' THEN 0 ELSE CAST(MA AS INT) END) AS MA,
    MAX(Speed) AS Speed
FROM @tblTest
GROUP BY Name,[No]



Uou can use below syntax to get your answer.

select sum(cast(columnname as int)) from TableName

Try applying this syntax,

select sum(cast(s.MA as int)) from (
Select
 RVU.Name,RVU.No,VV.Model,count(VV.Model) as total, RVU.MA as MA,RVU.Speed 
from 
 VV 
inner join RVU
on VV.MID=RVU.ID 
inner join RU on RVU.RID= RU.RID
WHERE 
RU.StartDate>= '2016-04-01 00:00:00' and
 RU.EndDate<='2016-04-30 23:59:59'   and
RU.Region= 'Paris'
and RVU.No= '651' AND Model <> ''
Group By RVU.Name,RVU.RegNo,VV.Model,RVU.MA,RVU.Speed ) S

Check this query, whether you were able to select your record and apply this logic in your query.

Your final query looks like,

Select 
S.Name,S.No,
SUM(Case when s.Model='Cultus' then total else 0 end) as Cultus,
SUM(Case when s.Model ='vigo' then total else 0 end) as vigo,
SUM(total) total_v ,
sum(cast(s.MA as int)),MAX(S.Speed) Speed
from (
Select
RVU.Name,RVU.No,VV.Model,count(VV.Model) as total, RVU.MA as MA,RVU.Speed 
from 
VV 
inner join RVU
on VV.MID=RVU.ID 
inner join RU on RVU.RID= RU.RID
WHERE 
RU.StartDate>= '2016-04-01 00:00:00' and
RU.EndDate<='2016-04-30 23:59:59'   and
RU.Region= 'Paris'
and RVU.No= '651' AND Model <> ''
Group By RVU.Name,RVU.RegNo,VV.Model,RVU.MA,RVU.Speed ) S
GROUP BY
s.RegNo,s.Name,S.MA


There may be a chance of string value in the column RVU.MA. Try out with the below query to find out such descrepancies.

   SELECT *
   FROM RVU
   WHERE ISNUMERIC (RVU.MA)!=1


This video can help you solving your question :)
By: admin