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
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
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