Guest User

Untitled

a guest
Jan 16th, 2018
295
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.16 KB | None | 0 0
  1. Table A Table B
  2. date | name | amount date | name | amount
  3. -----------+------+-------- -----------+------+--------
  4. 2018-01-16 | A | 2 <== 2018-01-16 | C | 1 tuesday
  5. 2018-01-16 | B | 3 2018-01-16 | A | 2 <==
  6. 2018-01-16 | A | 4 <==
  7. -----------+------+-------- -----------+------+--------
  8. 2018-01-11 | A | 8 <== thursday
  9. -----------+------+-------- -----------+------+--------
  10. 2018-01-02 | A | 2 <== tuesday
  11. 2018-01-02 | A | 10 <==
  12. -----------+------+-------- -----------+------+--------
  13. 2017-12-25 | C | 4 2017-12-25 | A | 2 <== monday
  14. -----------+------+-------- -----------+------+--------
  15. 2017-12-26 | A | 7 <== tuesday
  16. -----------+------+-------- -----------+------+--------
  17. 2017-12-07 | A | 9 <== thursday
  18.  
  19. SELECT
  20. t.`day` AS `day`,
  21. SUM(t.`amount`) AS `Atotal`
  22. FROM (
  23. (
  24. SELECT
  25. WEEKDAY(`date`) AS `day`,
  26. `amount` FROM `tableA`
  27. WHERE `name`="A"
  28. AND `date` >= (DATE("2018-01-16") - INTERVAL 6 DAY)
  29. )
  30. UNION ALL
  31. (
  32. SELECT
  33. WEEKDAY(`date`) AS `day`,
  34. `amount` FROM `tableB`
  35. WHERE `name`="A"
  36. AND `date` >= (DATE("2018-01-16") - INTERVAL 6 DAY)
  37. )
  38. ) t
  39. GROUP BY t.`day`
  40.  
  41. day | Atotal
  42. ----+------
  43. 1 | 8 (tuesday: 2018-01-16, 2 + 4 + 2)
  44. 3 | 8 (thursday: 2018-01-11, 8)
  45.  
  46. day | Dtot | Atotal
  47. ----+------+------
  48. 0 | 1 | 2 (2017-12-25) | (2)
  49. 1 | 3 | 27 (2018-01-16, 2018-01-02, 2017-12-26) | (2+4+2+2+10+7)
  50. 3 | 2 | 17 (2018-01-11, 2017-12-7) | (8+9)
  51.  
  52. SELECT
  53. r.`q_day` AS `day`,
  54. COUNT(r.`q_day`) AS `Dtot`
  55.  
  56. FROM(
  57. SELECT
  58. WEEKDAY(q.`p_day`) AS `q_day`
  59. FROM
  60. (
  61. SELECT DISTINCT `p_day`
  62. FROM (
  63. (SELECT DISTINCT `date` AS `p_day` FROM `tableA`)
  64. UNION
  65. (SELECT DISTINCT `date` AS `p_day` FROM `tableB`)
  66. ) AS p
  67. ) AS q
  68. ) AS r
  69. GROUP BY r.q_day
Add Comment
Please, Sign In to add comment