CODEWITHSUNDEEP
mysqlsql
Jonathan
Jonathan

Reputation: 3534

Counts from three tables in a single query?

I've got three tables: invoices, payments, and refunds. I'd like to get a txn count by type, grouped by month/year.

I'm looking for results like:

Month-Year    Invoices    Payments    Refunds
01-2013        32432        534        32
02-2013        4323        789         53

I can get a single tables count (minus some casting) with something like:

SELECT
    YEAR(txndate)
    , MONTH(txndate)
    , COUNT(*)
    , "Invoice" AS type
FROM invoices
GROUP BY
    YEAR(txndate)
   , MONTH(txndate)

If it comes down to it, I can just make three queries (one to each table) and then consolidate my results, but I figure there's got to be a smarter way.

Each table has a TxnDate column.

Any suggestions?

Upvotes: 1

Views: 68

Answers (2)

Mureinik
Mureinik

Reputation: 311428

Not sure this is really easier, but you could construct something like that with a bunch of UNIONs:

SELECT year, month, SUM(invoice) AS invoices, SUM(payment) AS payments, SUM(refund) AS refunds
FROM (SELECT YEAR(txndate) AS year, MONTH(txndate) AS month, 
             1 AS invoice, 0 AS payment, 0 AS refund
      FROM   invoices 
      UNION ALL
      SELECT YEAR(txndate) AS year, MONTH(txndate) AS month, 
             0 AS invoice, 1 AS payment, 0 AS refund
      FROM   payments
      UNION ALL
      SELECT YEAR(txndate) AS year, MONTH(txndate) AS month, 
             0 AS invoice, 0 AS payment, 1 AS refund
      FROM   refunds) t
GROUP BY year, month

Upvotes: 2

Cruncher
Cruncher

Reputation: 7812

You could try something like this, putting selects before the from

SELECT

(SELECT COUNT(*)
FROM invoices
WHERE YEAR(txndate) = YEAR(main.txndate) AND MONTH(txndate) = MONTH(main.txndate))
as invoices,

(SELECT COUNT(*)
FROM payments
WHERE YEAR(txndate) = YEAR(main.txndate) AND MONTH(txndate) = MONTH(main.txndate))
as payments,

(SELECT COUNT(*)
FROM refunds
WHERE YEAR(txndate) = YEAR(main.txndate) AND MONTH(txndate) = MONTH(main.txndate))
as refunds

FROM invoices as main
GROUP BY YEAR(txndate), MONTH(txndate)

Upvotes: 0

Related Questions