Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- mysql> SELECT
- -> DATE_FORMAT(s.created, "%Y-%m-%d") as "Date",
- -> count(s.id) AS "Accounts credited",
- -> sum(s.withdrawal) "Total Credited"
- -> -- 100 * (sum(s.withdrawal) - sum(prev.withdrawal))
- -- / sum(prev.withdrawal) "Difference in %"
- -> FROM statements s
- -> -- LEFT JOIN prev
- -> -- s.created - interval 7 DAY
- -> -- ON prev.created = s.created - interval 7 DAY
- -- AND (prev.status_id = 'OPEN'
- -- OR prev.status_id = 'PENDING')
- -> WHERE (s.status_id = 'OPEN' OR s.status_id = 'PENDING')
- -> GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
- -> ORDER BY s.created DESC
- -> LIMIT 8;
- +------------+-------------------+----------------+
- | Date | Accounts credited | Total Credited |
- +------------+-------------------+----------------+
- | 2019-01-19 | 18175 | 3173.68 |
- | 2019-01-12 | 18135 | 4768.43 |
- | 2019-01-05 | 17588 | 6968.49 |
- | 2018-12-29 | 17893 | 5404.18 |
- | 2018-12-22 | 17353 | 7048.18 |
- | 2018-12-15 | 16893 | 7181.34 |
- | 2018-12-08 | 16220 | 9547.09 |
- | 2018-12-01 | 15476 | 7699.59 |
- +------------+-------------------+----------------+
- 8 rows in set (0.79 sec)
Add Comment
Please, Sign In to add comment