oracle - SQL query to get current and last year sales -


i have following table sales:

date        store   sales 1/1/2015    st01    12123 1/1/2015    st02    3123 1/1/2016    st01    4213 1/1/2016    st03    2134 

when try self join year , last year sales closed store not showing up. result should this:

date        store   year sales   last year sales 1/1/2016    st01    4213              1212 1/1/2016    st02    0                 3123 1/1/2016    st03    2134              0 

my query follows:

select cy.date,         cy.store cy.sales,         ly.sales    sales cy,         sales ly   cy.store(+) = ly.store(+)         , ly.date = cy.date - 365  

oracle setup:

create table sales ( "date", store, sales ) select date '2015-01-01', 'st01', 12123 dual union select date '2015-01-01', 'st02',  3123 dual union select date '2016-01-01', 'st01',  4213 dual union select date '2016-01-01', 'st03',  2134 dual; 

query:

select trunc( sysdate, 'yy' ) "date",        store,        sum( case when "date" = trunc( sysdate, 'yy' )                  sales end )          "this year sales",        sum( case when "date" = add_months( trunc( sysdate, 'yy' ), -12 )                  sales end )          "last year sales"   sales group store order store; 

output:

date                store year sales last year sales ------------------- ----- --------------- --------------- 2016-01-01 00:00:00 st01             4213           12123  2016-01-01 00:00:00 st02                             3123  2016-01-01 00:00:00 st03             2134                  

Comments

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

android - Robolectric "INTERNET permission is required" -

java - Android raising EPERM (Operation not permitted) when attempting to send UDP packet after network connection -