SQL - Get the sum of several groups of records

Question

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)

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]

`SUM([s].[Hours])` clashes with `GROUP BY [s].[Hours]`. You typically GROUP BY the same columns as you SELECT, _except_ those who are arguments to set functions. — jarlh, Jun 15 '21 at 11:50
Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — SMor, Jun 15 '21 at 12:26

score 1 · Accepted Answer · answered Jun 15 '21 at 11:55

1

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

answered Jun 15 '21 at 11:55

Squirrel

23,507
4
34
32

Exactly what I needed. Thanks for helping without the free insults. – DreamTeK Jun 16 '21 at 08:36

score -1 · Answer 2 · answered Jun 15 '21 at 11:51

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.

SQL - Get the sum of several groups of records

2 Answers2