CODEWITHSUNDEEP
sqloracle-database
Tyvain
Tyvain

Reputation: 2750

Sql oracle group by and condition

I have these data:

year code points
-------------
2017  M    1
2018  L    3
2019  L    5

I need the total of points of 2019, taking all codes before 2019 that match the code of 2019

so the result should be:

y     c  p
----------
2019  L  8

I have an oracle database, not sure how to do it (with 'connect by' ?)

To test the resquest easily:

WITH test AS (
SELECT 2017 y, 'M' code, 1 point FROM DUAL
UNION
SELECT 2018 y, 'L' code, 3 point FROM DUAL
UNION 
SELECT 2019 y, 'L' code, 5 point FROM DUAL
)
SELECT * FROM test

Upvotes: 0

Views: 33

Answers (2)

Popeye
Popeye

Reputation: 35900

You can do a self join to achieve the desired result as follows:

SQL> WITH test AS (
  2  SELECT 2017 y, 'M' code, 1 point FROM DUAL
  3  UNION
  4  SELECT 2018 y, 'L' code, 3 point FROM DUAL
  5  UNION
  6  SELECT 2019 y, 'L' code, 5 point FROM DUAL
  7  )
  8  SELECT
  9      T1.Y,
 10      T1.POINT + COALESCE(T2.POINT,0) AS POINTS
 11  FROM
 12      TEST T1
 13      LEFT JOIN TEST T2 ON T1.CODE = T2.CODE
 14                           AND T1.Y > T2.Y
 15  WHERE T1.Y = '2019'
 16  ;

         Y     POINTS
---------- ----------
      2019          8

SQL>

Or you can use the analytical function as follows:

SQL> WITH test AS (
  2  SELECT 2017 y, 'M' code, 1 point FROM DUAL
  3  UNION
  4  SELECT 2018 y, 'L' code, 3 point FROM DUAL
  5  UNION
  6  SELECT 2019 y, 'L' code, 5 point FROM DUAL
  7  )
  8  SELECT Y, POINTS FROM
  9  (SELECT
 10      T1.Y,
 11      SUM(T1.POINT) OVER (PARTITION BY CODE ORDER BY Y) AS POINTS
 12  FROM
 13      TEST T1)
 14  WHERE Y = '2019'
 15  ;

         Y     POINTS
---------- ----------
      2019          8

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think I would go for:

select sum(points)
from (select code, sum(points) as points
      from t
      where y <= 2019
      group by code
      having max(y) = 2019
     ) x;

This handles two edge cases:

  • It works if there are years after 2019.
  • It works if there are multiple rows for a code in a given year.

Upvotes: 0

Related Questions