Hello!

There is such a thing:

It is necessary to display the profit for 30 days from the 3 tables at the same time on the chart, each table is responsible for its product, that is, in the database I have to get the following array('date' => $date, 'table_x_sum => $sumX, 'table_y_sum' => $sumY, 'table_z_sum' => $sumZ);

If you take from one table, you get the following query:

 'SELECT DATE(date) as DATE, SUM(replace(`summa`, ",", ".")) shopSum FROM shop_order GROUP BY DATE(date) DESC LIMIT 0, 30'

How do I make a JOIN , another 2 tables by date?

  • Do you guarantee that there are NO SKIPS in the table by date? - Akina
  • No, it is quite possible that there were no purchases on some days - Alexander Reizan

1 answer 1

 SELECT q0.date , COALESCE(q1.shopSum, 0) shopSum1 , COALESCE(q2.shopSum, 0) shopSum2 , COALESCE(q3.shopSum, 0) shopSum3 FROM ( SELECT DATE(date) date FROM table1 UNION SELECT DATE(date) FROM table2 UNION SELECT DATE(date) FROM table3 ) q0 LEFT JOIN ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table1 GROUP BY DATE(date) ) q1 ON q0.date = q1.date LEFT JOIN ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table2 GROUP BY DATE(date) ) q2 ON q0.date = q2.date LEFT JOIN ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table3 GROUP BY DATE(date) ) q3 ON q0.date = q3.date ORDER BY q0.date DESC LIMIT 0, 30

If the absence of "gaps" in the dates is guaranteed, it can be simplified to

 SELECT q1.date , q1.shopSum shopSum1 , q2.shopSum shopSum2 , q3.shopSum shopSum3 FROM ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table1 GROUP BY DATE(date) ) q1 JOIN ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table2 GROUP BY DATE(date) ) q2 ON q1.date = q2.date JOIN ( SELECT DATE(date) date , SUM(replace(`summa`, ",", ".")) shopSum FROM table3 GROUP BY DATE(date) ) q3 ON q1.date = q3.date ORDER BY q1.date DESC LIMIT 0, 30

If it is possible that for some date there were no purchases in all three stores, but the data for this date, even if they are zero, is needed - a reference table of dates is needed (possibly dynamically generated). Type:

 ( SELECT DATEADD('d', -q02.delta, MAX(q01.date)) date FROM ( SELECT MAX(DATE(date)) date FROM table1 UNION SELECT MAX(DATE(date)) FROM table2 UNION SELECT MAX(DATE(date)) FROM table3 ) q01, ( SELECT xx.x*6+yy.y delta FROM (SELECT 0 x UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xx, (SELECT 0 y UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) yy ) q02 )q0
  • Thank you very much! I myself would definitely not have managed! - Alexander Reizan
  • In the first case, select dates separately and then on them, in the second pass, glue the results a bit redundantly. I think it’s simpler that something like pastebin.com/Q0T1NWkT, if desired, if the optimizer is suddenly easier, you can group the union parts each up to the date by itself and then group it all together again - Mike