I have two tables
- date with columns id, year, month
- transactions with columns id, amount, type, income, instrument
type can be only either debit or credit and instrument can be any method like credit card etc.
What I need is to get a query which select year, month,type, instrument and the sum of 'amount' grouped by type and instrument as well as sum of income grouped by year and month.
I can get values using two different queries
example:
to get the sum of income grouped by year and month:
select sum(T.income) as total
from transaction as T, date as D
where D.id = T.id
group by D.month, D.year)
And to get other values:
select D.year, D.month,
T.type, T.instrument, sum(T.amount) as sumAmount,T.income
from date as D, transaction as T
where D.id=T.id,
group by T.instrument, T.type
but I need to get it done by a single query. Is there another way to retrieve this data set? Is it possible to use group by in two ways in the same select statement?