CODEWITHSUNDEEP
sqlsql-serversumsql-max
DreamTeK
DreamTeK

Reputation: 34297

SQL - Get the sum of several groups of records

DESIRED RESULT

Get the hours SUM of all [Hours] including only a single result from each [DevelopmentID] where [Revision] is highest value

e.g SUM 1, 2, 3, 5, 6 (Result should be 22.00)

SQL MAX

I'm stuck trying to get the appropriate grouping.

DECLARE @CompanyID INT = 1

SELECT  
  SUM([s].[Hours]) AS [Hours]
FROM
  [dbo].[tblDev] [d] WITH (NOLOCK)
JOIN
  [dbo].[tblSpec] [s] WITH (NOLOCK) ON [d].[DevID] = [s].[DevID]
WHERE   
  [s].[Revision] = (
    SELECT MAX([s2].[Revision]) FROM [tblSpec] [s2]
  )
GROUP BY
  [s].[Hours]

Upvotes: 0

Views: 45

Answers (2)

Squirrel
Squirrel

Reputation: 24793

use row_number() to identify the latest revision

SELECT SUM([Hours])
FROM   (
           SELECT *, R = ROW_NUMBER() OVER (PARTITION BY d.DevID
                                                ORDER BY s.Revision)
           FROM   [dbo].[tblDev] d
           JOIN   [dbo].[tblSpec] s 
             ON   d.[DevID] = s.[DevID]
       ) d
WHERE  R = 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270513

If you want one row per DevId, then that should be in the GROUP BY (and presumably in the SELECT as well):

SELECT s.DevId, SUM(s.Hours) as hours
FROM [dbo].[tblDev] d JOIN
     [dbo].[tblSpec] s
     ON [d].[DevID] = [s].[DevID]
WHERE s.Revision = (SELECT MAX(s2.Revision) FROM tblSpec s2)
GROUP BY s.DevId;

Also, don't use WITH NOLOCK unless you really know what you are doing -- and I'm guessing you do not. It is basically a license that says: "You can get me data even if it is not 100% accurate."

I would also dispense with all the square braces. They just make the query harder to write and to read.

Upvotes: -1

Related Questions