CODEWITHSUNDEEP
sqlsql-serversum
user6628729
user6628729

Reputation: 323

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

Upvotes: 2

Views: 235

Answers (4)

The Shooter
The Shooter

Reputation: 733

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

Upvotes: 1

sandeep
sandeep

Reputation: 7

select ID,OName ,RegNo,count(vname) as total,Max(MA) as MA,Max(MSpeed) as Mspeed from table_name group by OName;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You probably intend COUNT() or COUNT(DISTINCT) rather than SUM(). You already have GROUP BY, so don't use SELECT DISTINCT:

Select tblRV.ID, tblRV.oName, tblRV.RegNo,
       count(distinct tblvv.VName) as total, -- number of different names
       max(tblRV.Speed),
-------^ Your comment suggests that this should be `SUM()`
       tblRV.MA
from tblRe inner join
     tblRV
     On tblRe.RID = tblRV.RID inner join
    tblvv 
     on tblRV.ID = tblVV.MID 
where tblRegion_Uni.StartDate >= '2016-07-01' AND 
      tblRegion_Uni.EndDate < '2016-07-29' and
      tblRegion_Uni.Region = 'uk' and
      VName <> ''
group by tblRV.ID, tblRV.OName, tblRV.RegNo, tblRV.MA
order by tblRV.OName, tblRV.ID

Upvotes: 1

Matt
Matt

Reputation: 15061

I think you want COUNT not SUM, also don't use DISTINCT with a GROUP BY.

Side note: tblRegion_Uni isn't in the FROM part? Side note 2: Could AND tblvv.VName <> '' be AND tblvv.VName IS NOT NULL

SELECT 
tblRV.ID AS ID, 
tblRV.oName,
tblRV.RegNo,
COUNT(tblvv.VName) AS total,
MAX(tblRV.Speed),
tblRV.MA
FROM tblRe
INNER JOIN tblRV ON tblRe.RID = tblRV.RID
INNER JOIN tblvv ON tblRV.ID = tblVV.MID 
WHERE tblRegion_Uni.StartDate >= '2016-07-01 00:00:00.000'
AND tblRegion_Uni.EndDate <= '2016-07-28  23:59:59.000'
AND tblRegion_Uni.Region = 'uk'
AND tblvv.VName <> ''
GROUP BY tblRV.ID, tblRV.OName, tblRV.RegNo, tblRV.MA
ORDER BY tblRV.OName, tblRV.ID

Upvotes: 0

Related Questions