Home » SQL & PL/SQL » SQL & PL/SQL » compute sum of balances based on year
compute sum of balances based on year [message #190703] Thu, 31 August 2006 20:40 Go to next message
Messages: 25
Registered: June 2006
Junior Member

the tables are: balance, student, active_period (which contains the current year and semester)

this query displays the total balance for the active year:

select b.stud_id, b.name, sum(a.balance) year1
from balance a, student b, active_period c
where a.stud_id = b.stud_id
and a.year = c.year
group by b.stud_id, b.name

how can i get the balances for the prior years? for example, how can i insert this statement into the main query?

select stud_id, sum(balance) year2
from balance a, active_period b
where a.year = b.year - 1
group by stud_id

the output should be like this:

stud_id name year1 year2 .....

Re: compute sum of balances based on year [message #190741 is a reply to message #190703] Fri, 01 September 2006 01:49 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No table definitions, no create table statements, no insert statements, no sample data, no sample results.
You really do want the world handing to you on a plate, don't you.

Assuming you can live without the Active_Period table in the query, something like this should do the trick

select b.stud_id
       ,sum(decode(a.year,<this year>,a.balance,0) this_year
       ,sum(decode(a.year,<last year>,a.balance,0) last_year
       ,sum(decode(a.year,<this year>,0,<last year>,0,a.balance) all_other_years_year
from balance a, student b
where a.stud_id = b.stud_id
group by b.stud_id, b.name
Previous Topic: Calls over gateways
Next Topic: SQL query
Goto Forum:

Current Time: Thu Aug 17 18:30:06 CDT 2017

Total time taken to generate the page: 0.06171 seconds