Combining two GROUP BY queries grouped by the same column

I have a transaction table, and a tag table. I want to get a sum of all transactions in the transaction table grouped by tag. There are two different types of transaction: "budget" and "actual"

This query will return me what I want for "budget" transactions:

  SELECT tg.name as tag, SUM(amount) as budgetTotal 
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN tag tg ON tg.id = tt.tag_id
   WHERE tx.type = "budget"
     AND tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
GROUP BY tg.name

And of course pretty much the same query for "actual" transactions:

  SELECT tg.name as tag, SUM(amount) as actualTotal 
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN ta开发者_运维问答g tg ON tg.id = tt.tag_id
   WHERE tx.type = "actual"
     AND tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
GROUP BY tg.name

My question: how do I group the results of these two queries, into one, so I get one results table with three columns: tag, budgetTotal and actualTotal?

Try this:

  SELECT tg.name, 
         CASE WHEN tx.type = "actual" THEN SUM(amount) END AS actualTotal,
         CASE WHEN tx.type = "budget" THEN SUM(amount) END AS budgetTotal
  FROM....
  WHERE  tx.type IN ("actual", "budget")
  AND   ....
  GROUP BY tg.name

SELECT tg.name as tag, SUM(amount) as budgetTotal, 'budget' as rectype
FROM transaction tx
JOIN transaction_tag tt ON tt.transaction_id = tx.id
JOIN tag tg ON tg.id = tt.tag_id
WHERE tx.type = "budget"
AND tx.date >= '2011-07-15' 
AND tx.date < '2011-08-15'
GROUP BY tg.name

UNION ALL

SELECT tg.name as tag, SUM(amount) as actualTotal, , 'actual' as rectype
FROM transaction tx
JOIN transaction_tag tt ON tt.transaction_id = tx.id
JOIN tag tg ON tg.id = tt.tag_id
WHERE tx.type = "actual"
AND tx.date >= '2011-07-15' 
AND tx.date < '2011-08-15'
GROUP BY tg.name

Not dissing the other answer (which is probably better), but here's how to get it as two separate rows if that suits. Also, this answer is scalable to any number of tx.type without changing the query (if you remove the reference to t.type in the where clause of course):

SELECT tg.name as tag, tx.type, SUM(amount) as total
    FROM transaction tx
    JOIN transaction_tag tt ON tt.transaction_id = tx.id
    JOIN tag tg ON tg.id = tt.tag_id
   WHERE tx.date >= '2011-07-15' 
     AND tx.date < '2011-08-15'
     AND tx.type in ("budget", "actual")
GROUP BY tg.name, tx.type;