SQL Query for Summary By Instances Per Day

Question

I've got a table in a mysql database that contains monetary transactions. Let's say the tabled is called transactions:

id int,
to_user int,
from_user int,
created datetime,
amount double

I'm trying to create a query that returns the average amount of a transaction grouped by the number of transactions that appear per day. The applicable columns are amount and created.

So I'm trying to answer the question: what is the average amount of transactions that occur on days where there are between 0 and 2 transactions, between 2 and 4 transactions, between 4 and 6 transactions, etc.

Any ideas?

Answer 1

After several misunderstandings I finally seem to get what you need :)

SELECT  cnt_range * 2 AS days_range,
        CASE WHEN SUM(trans_cnt) > 0 THEN
             SUM(trans_sum) / SUM(trans_cnt)
             ELSE 0
        END AS average_amount
FROM    (
        SELECT  SUM(amount) AS trans_sum,
                COUNT(*) AS trans_cnt,
                FLOOR(COUNT(*) / 2) AS cnt_range
        FROM    transactions
        GROUP BY
                TO_DATE(created)
        ) ao
GROUP BY
        cnt_range

Answer 2

Here's a try (but not tested):

SELECT CASE WHEN txn_per_day BETWEEN 0 AND 2 THEN 2
            WHEN txn_per_day BETWEEN 2 AND 4 THEN 4
            WHEN txn_per_day BETWEEN 4 AND 6 THEN 6
            ELSE 8 -- or more
       END CASE AS num_txns, AVG(t3.amount)
FROM (
  SELECT t1.*, COUNT(*) AS txn_per_day
  FROM transactions t1 JOIN transactions t2 
    ON (TO_DAYS(t1.created) = TO_DAYS(t2.created))
  GROUP BY t1.id
) t3
GROUP BY num_txns;

This is not especially speedy. It'd be okay for generating a report, but if you need it to run during a PHP request for instance, it's not great.

Answer 3

SELECT AVG(`num`), ((`num` - 1) DIV 2) * 2 AS `tier`
FROM (
    SELECT DATE_FORMAT(`created`, '%Y-%m-%d') AS `day`, COUNT(*) AS `num`
    FROM `yourtable`
    GROUP BY 1
) AS `src`
GROUP BY `tier`

Answer 4

Try something like

SELECT AVG(`amount`) as `amount`,
    DATE_FORMAT(`created`, '%Y-%m-%d') as `date`
FROM `transactions`
GROUP BY DATE_FORMAT(`created`, '%Y-%m-%d')

Look some more here at Date-and-time functions and check out the BETWEEN function too