none
Need Query the sum of values with a condition RRS feed

  • Question

  • Hello;

    I need a query that I will try to explain with the following example:

    i have a Tb1 Table:

    Tb1

    -----------------------------------------------------

    ID                 Prsid              year                 Wage

    -----------------------------------------------------

    1                    1                 2017                 100

    2                    2                 2016                 150

    3                    3                 2018                  80

    4                    2                 2018                 250

    5                    1                 2018                 180

    6                    1                 2019                 200

    ****************************************

    Question:

    The values in the wage column for each PrsID member should be selected for the Largest year available and then aggregated.

    for prsid =1     year =2019   selected  

    for prsid= 2     year =2018   selected

    for prsid =3     year =2018    selected

    SUM(WAGE)= 200 + 250 + 80

    thanks

    Sunday, November 10, 2019 6:09 AM

Answers

  • Try it with

    ;WITH cte AS
        (SELECT Prsid, MAX(year) AS MaxYear
         FROM yourTable
         GROUP BY Prsid)
    SELECT SUM(T1.Wage)
    FROM yourTable AS T1
         INNER JOIN
         cte
             ON T1.Prsid = cte.Prsid
                and T1.year = cte.MaxYear


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Ashkan209 Sunday, November 10, 2019 7:31 PM
    Sunday, November 10, 2019 8:14 AM