Use two group by with one select statement

Viewed 14969

I have two tables

  1. date with columns id, year, month
  2. 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?

1 Answers
Related