How to find same value in same table without hardcoding in where clause?

Shah Published at Dev

shah

My table looks like this:

User  | Sale|  Year
-------------------------------
Kim   |  2  |  2019
Kim   |  2  |  2018
Kim   |  1  |  2017
Tim   |  3  |  2019
Tim   |  2  |  2018
Tim   |  1  |  2017
Jim   |  4  |  2019
Jim   |  3  |  2018
Jim   |  3  |  2017

There are many records in the table. I want to add the number of sales in last years for each user.

Result should be like this:

Name | Current Year | Previous Years(Sum)
Kim  |      2       |       3
Tim  |      3       |       3
Jim  |      4       |       6

I have tried this so far but no luck

select 
User,
Sale as CurrentYearSale,
sum(case when Year < 2019 then cast(Sale as int) end) as PreviousYearsSale
from 
Test 
where Year =2019
group by User,Sale;

I cant hardcode the name

Update:

I am getting result like :

   User   CurrentYearSale  PreviousYear Sale
  ---------------------------------------------------
    Sam         3                    NULL
    Sam        NULL                  2
    Kim         4                    NULL
    Kim        NULL                   5
    Tim          2                   NULL
    Tim        NULL                    4

mkRabbani

Try conditional aggregation as shown below-

SELECT [User],
SUM(CASE WHEN [Year] = 2019 THEN Sale ELSE 0 END) C_year,
SUM(CASE WHEN [Year] < 2019 THEN Sale ELSE 0 END) P_year
FROM your_table
GROUP BY  [User]

You can check DEMO HERE

Your given query should be written as below as well-

select [User],
sum(case when [Year] = 2019 then cast(Sale as int) ELSE 0 end) as   CurrentYearSale,
sum(case when [Year] < 2019 then cast(Sale as int) ELSE 0 end) as PreviousYearsSale
from Test 
--where Year =2019
-- As your are calculating sum for previous year
-- you do not need to filter data by Year = 2019
group by [User];

You can check the output of your query HERE

Collected from the Internet

Please contact [email protected] to delete if infringement.