sql - Sum of two columns with join and group by another column -

i need t-sql query. i'm trying following:

in result want 1 column grouped store no. in second column want sum of net amount sales , third column want show sales last year each store no.

+-------+--------+------------+ | store | sales  | last year  | +-------+--------+------------+ |  401  | 20000  |   19000    | |  402  | 25000  |   21000    | |  403  | 10000  |   15000    | +-------+--------+------------+ 

note: sales year in table called "trans_ sales entry" , sales last year archived in table called "archived sales entry"

i don't want type in date manually in query, rather want variable example ("today" , "today"-365)

is right way it, or there better ways?

select    "company$trans_ sales entry"."store no_" "store",    sum("company$trans_ sales entry"."net amount"*-1) "sales",    sum("company$archived sales entry"."net amount"*-1) "last year" "company$trans_ sales entry" join "company$archived sales entry" on   "company$trans_ sales entry"."store no_"="company$archived sales entry"."store no_"    "company$trans_ sales entry"."date"='2014-05-16' ,   "company$archived sales entry"."date"='2013-05-16' group   "company$trans_ sales entry"."store no_",   "company$archived sales entry"."store no_" 

when execute query numbers in "sales" , "last year" way high. wrong in query... hope can help!

if execute sales figures correct have not included last year column:

 select       [company$trans_ sales entry].[store no_] store,       sum([company$trans_ sales entry].[net amount]) sales         [company$trans_ sales entry]         [company$trans_ sales entry].date = '2014-05-16'   group       [company$trans_ sales entry].[store no_] 

there multiple rows per store in both trans , archived tables why need sum rows , group them per store in both tables.

i suspect have more 1 row per store in both trans , archived tables. if so, query doomed multiply results, engine first joins tables creating cartesian result of trans rows , archived rows of same store, , sums values. if have 2 trans rows , 3 archived rows 1 store, 6 rows before aggregation... , after aggregation , sum, "sales" 3 times expect , "last year" twice expect (for store).

this query should solve problem.

select   "store no_" "store",   sum("net amount"*-1) "sales",   (select sum("net amount"*-1)     "company$archived sales entry"           "store no_"="company$trans_ sales entry"."store no_" ,       "date"='2013-05-16'    )  "last year" "company$trans_ sales entry" "date"='2014-05-16' group "store no_" 

it aggregates , sums trans table on store, adding each store column calculated sum on archived table... subselect.

another approach first aggregate , sum 2 tables, , join result on store:

select t.store, sales, "last year" (select "store no_" "store", sum("net amount"*-1) "sales"       "company$trans_ sales entry"       "date"='2014-05-16'       group "store no_") t join (select "store no_" "store", sum("net amount"*-1) "last year"       "company$archived sales entry"       "date"='2013-05-16'       group "store no_") on a.store=t.store order t.store 

notice there difference in results if trans table , archived table not contain same stores. first list stores found in trans table. second list stores found in both tables.