CODEWITHSUNDEEP
sqlitesubquery
Goldentoa11
Goldentoa11

Reputation: 1760

Joining Sqlite table on Subqueries

I'm trying to join a sqlite table based on an grouping column, 'year'. I've started with a select clause that returns all the years in the database, and now I want to subquery the same table for various statistics and join it based on the year. The following is what I have.

select strftime('%Y', `date`, 'unixepoch') as `year` from transactions as t

left join (
    select sum(amount) as `expenses`, 
    strftime('%Y', `date`, 'unixepoch') as `year` from transactions
    where type = -1 and user_id = 1
    group by strftime('%Y', `date`, 'unixepoch')
) as e on e.year = t.year

left join (
    select sum(amount) as `income`, 
    strftime('%Y', `date`, 'unixepoch') as `year` from transactions
    where type = 1 and user_id = 1
    group by strftime('%Y', `date`, 'unixepoch')
) as i on i.year = t.year

group by strftime('%Y', `date`, 'unixepoch');

When I try to run that sql statement, I get 'no such column: t.year'. Why can't I join based on the aliased column?

Upvotes: 0

Views: 562

Answers (1)

forpas
forpas

Reputation: 164204

The table transactions aliased as t does not contain a column year (which is a derived column).
Change to this:

select strftime('%Y', t.`date`, 'unixepoch') as `year` from transactions as t

left join (
    select sum(amount) as `expenses`, 
    strftime('%Y', `date`, 'unixepoch') as `year` from transactions
    where type = -1 and user_id = 1
    group by strftime('%Y', `date`, 'unixepoch')
) as e on e.year = strftime('%Y', t.`date`, 'unixepoch')

left join (
    select sum(amount) as `income`, 
    strftime('%Y', `date`, 'unixepoch') as `year` from transactions
    where type = 1 and user_id = 1
    group by strftime('%Y', `date`, 'unixepoch')
) as i on i.year = strftime('%Y', t.`date`, 'unixepoch')

group by strftime('%Y', t.`date`, 'unixepoch');

I guess there are other columns from the joined tables that you want to return although not in the current code.

I think that this is what you want to achieve:

select 
  strftime('%Y', t.`date`, 'unixepoch') as `year` ,
  sum(case when type = -1 and user_id = 1 then amount end) as `expenses`,
  sum(case when type = 1 and user_id = 1 then amount end) as `income`
from transactions as t
group by strftime('%Y', t.`date`, 'unixepoch');

Upvotes: 1

Related Questions