Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Table A Table B
- date | name | amount date | name | amount
- -----------+------+-------- -----------+------+--------
- 2018-01-16 | A | 2 <== 2018-01-16 | C | 1 tuesday
- 2018-01-16 | B | 3 2018-01-16 | A | 2 <==
- 2018-01-16 | A | 4 <==
- -----------+------+-------- -----------+------+--------
- 2018-01-11 | A | 8 <== thursday
- -----------+------+-------- -----------+------+--------
- 2018-01-02 | A | 2 <== tuesday
- 2018-01-02 | A | 10 <==
- -----------+------+-------- -----------+------+--------
- 2017-12-25 | C | 4 2017-12-25 | A | 2 <== monday
- -----------+------+-------- -----------+------+--------
- 2017-12-26 | A | 7 <== tuesday
- -----------+------+-------- -----------+------+--------
- 2017-12-07 | A | 9 <== thursday
- SELECT
- t.`day` AS `day`,
- SUM(t.`amount`) AS `Atotal`
- FROM (
- (
- SELECT
- WEEKDAY(`date`) AS `day`,
- `amount` FROM `tableA`
- WHERE `name`="A"
- AND `date` >= (DATE("2018-01-16") - INTERVAL 6 DAY)
- )
- UNION ALL
- (
- SELECT
- WEEKDAY(`date`) AS `day`,
- `amount` FROM `tableB`
- WHERE `name`="A"
- AND `date` >= (DATE("2018-01-16") - INTERVAL 6 DAY)
- )
- ) t
- GROUP BY t.`day`
- day | Atotal
- ----+------
- 1 | 8 (tuesday: 2018-01-16, 2 + 4 + 2)
- 3 | 8 (thursday: 2018-01-11, 8)
- day | Dtot | Atotal
- ----+------+------
- 0 | 1 | 2 (2017-12-25) | (2)
- 1 | 3 | 27 (2018-01-16, 2018-01-02, 2017-12-26) | (2+4+2+2+10+7)
- 3 | 2 | 17 (2018-01-11, 2017-12-7) | (8+9)
- SELECT
- r.`q_day` AS `day`,
- COUNT(r.`q_day`) AS `Dtot`
- FROM(
- SELECT
- WEEKDAY(q.`p_day`) AS `q_day`
- FROM
- (
- SELECT DISTINCT `p_day`
- FROM (
- (SELECT DISTINCT `date` AS `p_day` FROM `tableA`)
- UNION
- (SELECT DISTINCT `date` AS `p_day` FROM `tableB`)
- ) AS p
- ) AS q
- ) AS r
- GROUP BY r.q_day
Add Comment
Please, Sign In to add comment