user6628729 user6628729 - 3 months ago 15
SQL Question

Sum in SQL query

I try this query to get VName sum. in table VName datatype is varchar

Select distinct tblRV.ID as ID,
tblRV.OName,
tblRV.RegNo,
tblvv.VName,
count( tblvv.VName) as total,
tblRV.MA,
tblRV.MSpeed
from tblRe
inner join tblRV
On tblReG.RID = tblRV.RID
inner join tblvv
on tblRV.ID=tblVV.MID
WHERE
tblRe.StartDate >= '2016-07-01 00:00:00.000' AND
tblRe.EndDate <= '2016-07-31 23:59:59.000' and
tblRe.Region = ‘UK’ and
VName <> ''
group by
tblRV.ID ,
tblRV.OName,
tblRV .RegNo,
tblRV.MA,
tblRV.MSpeed,
tblRV.VName
order by
tblRV.OName,
tblRV.ID


WHEN I TRY above query this show result like this

ID OName RegNo VName total MA MSpeed
26626 john BE MV 3 754 130
26765 john BE MV 3 90 140
24569 john BE DDSB 6 4 19
27569 john BE MV 2 62 129
21231 john BE MV 3 66 136


now i want to omit VName and want all total of VName AND MAX value of MA and MSpeed

i.e.

ID OName RegNo total MA MSpeed
26626 john BE 17 754 136


for i try @The Shooter query query in this query i only add top(1) so when i add this show total 3 whereas i want total 17

SELECT
S.ID
,S.OName
,S.RegNo
,SUM(Total) vNameSum
,MAX(S.MA) MaxMA
,MAX(S.MSpeed) MaxMSpeed
FROM
(
SELECT top (1)
tblRV.ID as ID,
tblRV.OName,
tblRV.RegNo,
tblvv.VName,
COUNT(tblvv.VName) as total,
tblRV.MA,
tblRV.MSpeed
FROM tblRe
INNER JOIN tblRV
ON tblReG.RID = tblRV.RID
INNER JOIN tblvv
ON tblRV.ID=tblVV.MID
WHERE
tblRe.StartDate >= '2016-07-01 00:00:00.000'
AND tblRe.EndDate <= '2016-07-31 23:59:59.000'
AND tblRe.Region = ‘UK’
AND VName <> ''
GROUP BY
tblRV.ID
,tblRV.OName
,tblRV.RegNo
,tblRV.MA
,tblRV.MSpeed
,tblRV.VName
ORDER BY
tblRV.OName
,tblRV.ID
) S
GROUP BY
S.ID
,S.OName
,S.RegNo


so above query show result like this

ID OName RegNo VName total MA MSpeed
26626 john BE MV 3 754 130


wheras i want total 17 not 3

Answer

Try this (I just modified your query):

SELECT 
    S.OName
    ,S.RegNo
    ,SUM(Total) vNameSum
    ,MAX(S.MA) MaxMA
    ,MAX(S.MSpeed) MaxMSpeed
FROM 
(
    SELECT  
        tblRV.OName,
        tblRV.RegNo,
        tblvv.VName,
        COUNT(tblvv.VName) as total, 
        tblRV.MA,
        tblRV.MSpeed
    FROM tblRe 
    INNER JOIN tblRV
    ON tblReG.RID = tblRV.RID
    INNER JOIN tblvv 
    ON tblRV.ID=tblVV.MID 
    WHERE 
        tblRe.StartDate >= '2016-07-01 00:00:00.000'
        AND tblRe.EndDate <= '2016-07-31  23:59:59.000'
        AND tblRe.Region = ‘UK’
        AND VName <> ''
    GROUP BY 
        tblRV.OName
        ,tblRV.RegNo
        ,tblRV.MA
        ,tblRV.MSpeed
        ,tblRV.VName
) S
GROUP BY
S.OName
,S.RegNo
ORDER BY
tblRV.OName