Use two group by with one select statement

Question

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?

Answer 1

Is this the one you are looking for?

SELECT tableA.ID, tableA.`Year`, tableA.`Month`,  
       tableA.`Type`, tableA.instrument, 
       tableA.totalAmount, tableB.totalInstrument
FROM
(
    SELECT  a.ID, a.`Year`, a.`Month`, 
            b.`Type`, b.instrument, 
            SUM(b.`amount`) totalAmount
    FROM    `date` a
                INNER JOIN `transactions` b
                    ON a.ID = b.id
    GROUP BY b.`Type
) tableA
INNER JOIN
(
    SELECT  a.ID, a.`Year`, a.`Month`, 
            b.`Type`, b.instrument, 
            SUM(b.`instrument`) totalInstrument
    FROM    `date` a
                INNER JOIN `transactions` b
                    ON a.ID = b.id
    GROUP BY a.`Year`, a.`Month`
) tableB ON tableA.ID = tableB.ID AND
            tableA.`Year` = tableB.`Year` AND
            tableA.`Month` = tableB.`Month`

the first subquery is by getting the sum of the amount and the second subquery gets the sum of the instrument. their results will be joined in order to get the totalAmount and totalInstrument in a row.