CODEWITHSUNDEEP
sqlsql-serversum
user6628729
user6628729

Reputation: 323

Operand data type varchar is invalid for sum operator

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

Upvotes: 1

Views: 20766

Answers (4)

Jim Macaulay
Jim Macaulay

Reputation: 5141

You 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

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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]

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

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

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28890

create table #t
(
id varchar(20))



insert into #t
select '2'
union all
select '3'

select sum(id) from #t--this fails

You need to do

select sum(cast (id as int))--cast from 2008 onwards
from #t

In your case MA column is of type Varchar

Upvotes: 0

Related Questions