CODEWITHSUNDEEP
sqlsql-servert-sql
wakeful
wakeful

Reputation: 163

SUM(), GROUP BY, and WHERE

I have a table like this:

Title   | Version   | Condition | Count
-----------------------------------------------
Title1  | 1.0       | 1         | 10
Title1  | 1.1       | 2         | 5
Title1  | 1.1       | 2         | 10
Title1  | 1.1       | 1         | 10
Title2  | 1.0       | 2         | 10
Title2  | 1.5       | 1         | 5
Title2  | 1.5       | 2         | 5
Title3  | 1.5       | 2         | 10
Title3  | 1.5       | 1         | 10

And I would like to sum the value of "Count" for each line that has the MAX() "Version", and "Condition" = 2. I'd like this to be the resulting data set:

Title   | Version   | Condition | Count
-----------------------------------------------
Title1  | 1.1       | 2         | 15
Title2  | 1.5       | 2         | 5
Title3  | 1.5       | 2         | 10

I am able to get the list of "Title" And MAX("Version") with "Condition" = 2 with:

SELECT DISTINCT Title, MAX(Version) AS MaxVer FROM TABLE
WHERE Condition = 2
GROUP BY Title

Bit I'm not sure how to add all the "Count"s.

Upvotes: 2

Views: 284

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT t1.Title, t1.Version, t1.Condition, SUM([Count]) AS Count
FROM mytable AS t1
JOIN (
  SELECT Title, MAX(Version) AS max_version
  FROM mytable
  WHERE Condition = 2
  GROUP BY Title
) AS t2 ON t1.Title = t2.Title AND t1.Version = t2.max_version
WHERE t1.Condition = 2
GROUP BY t1.Title, t1.Version, t1.Condition

Demo here

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

This needs only a single access to the table:

SELECT Title, Version, Condition, Count
FROM
 ( -- calculate all SUMs
   SELECT Title, Version, Condition, SUM(Count) AS Count,
      ROW_NUMBER() OVER (PARTITION BY Title ORDER BY Version DESC) AS rn
   FROM TABLE
   WHERE Condition = 2
   GROUP BY Title, Version, Condition
 ) AS dt
   -- and return only the row with the highest Version
WHERE rn = 1

Upvotes: 1

Related Questions