Window Function - an old SQL standard that is now available in MySQL
-- tested with MariaDB 10.2, MySQL 8.0 -- Other RDBMS already know Window Functions for years SELECT name, status, date, SUM(price) OVER (PARTITION BY name) as price_name_sum, SUM(price) OVER (PARTITION BY date) as price_date_sum FROM history WHERE status = 1 AND date BETWEEN '2015-01-01' AND '2015-04-30' ORDER BY date, name;
Result
| name | status | date | price_name_sum | price_date_sum |
|---|---|---|---|---|
| baz | 1 | 2015-01-02 00:00:00 | 15580.485020 | 1614.485820 |
| coffee | 1 | 2015-01-02 00:00:00 | 11318.022050 | 1614.485820 |
| qux | 1 | 2015-01-02 00:00:00 | 8692.237650 | 1614.485820 |
| foo | 1 | 2015-01-04 00:00:00 | 9499.713070 | 447.617910 |
| foo | 1 | 2015-01-05 00:00:00 | 9499.713070 | 806.781750 |
| quux | 1 | 2015-01-05 00:00:00 | 15543.947200 | 806.781750 |
| baz | 1 | 2015-01-06 00:00:00 | 15580.485020 | 1173.386860 |
| budgie | 1 | 2015-01-06 00:00:00 | 12117.415700 | 1173.386860 |
| foo | 1 | 2015-01-06 00:00:00 | 9499.713070 | 1173.386860 |
| bar | 1 | 2015-01-07 00:00:00 | 6224.922990 | 1550.231380 |
| baz | 1 | 2015-01-07 00:00:00 | 15580.485020 | 1550.231380 |
| quux | 1 | 2015-01-07 00:00:00 | 15543.947200 | 1550.231380 |
| quux | 1 | 2015-01-08 00:00:00 | 15543.947200 | 43.043040 |
| baz | 1 | 2015-01-10 00:00:00 | 15580.485020 | 721.507360 |
| coffee | 1 | 2015-01-11 00:00:00 | 11318.022050 | 858.344090 |
| foo | 1 | 2015-01-11 00:00:00 | 9499.713070 | 858.344090 |
| baz | 1 | 2015-01-12 00:00:00 | 15580.485020 | 230.571160 |
| baz | 1 | 2015-01-14 00:00:00 | 15580.485020 | 987.128940 |
| bar | 1 | 2015-01-15 00:00:00 | 6224.922990 | 1028.040530 |
| budgie | 1 | 2015-01-15 00:00:00 | 12117.415700 | 1028.040530 |
| baz | 1 | 2015-01-16 00:00:00 | 15580.485020 | 802.564650 |
| budgie | 1 | 2015-01-16 00:00:00 | 12117.415700 | 802.564650 |
| quux | 1 | 2015-01-16 00:00:00 | 15543.947200 | 802.564650 |
| budgie | 1 | 2015-01-19 00:00:00 | 12117.415700 | 212.390520 |
| baz | 1 | 2015-01-20 00:00:00 | 15580.485020 | 976.670860 |
| budgie | 1 | 2015-01-20 00:00:00 | 12117.415700 | 976.670860 |
| quux | 1 | 2015-01-21 00:00:00 | 15543.947200 | 99.940590 |
| bar | 1 | 2015-01-22 00:00:00 | 6224.922990 | 209.280850 |
| baz | 1 | 2015-01-23 00:00:00 | 15580.485020 | 1530.191290 |
| foo | 1 | 2015-01-23 00:00:00 | 9499.713070 | 1530.191290 |
| baz | 1 | 2015-01-24 00:00:00 | 15580.485020 | 2250.339800 |
| budgie | 1 | 2015-01-24 00:00:00 | 12117.415700 | 2250.339800 |
| quux | 1 | 2015-01-24 00:00:00 | 15543.947200 | 2250.339800 |
| qux | 1 | 2015-01-24 00:00:00 | 8692.237650 | 2250.339800 |
| baz | 1 | 2015-01-25 00:00:00 | 15580.485020 | 1698.625640 |
| budgie | 1 | 2015-01-25 00:00:00 | 12117.415700 | 1698.625640 |
| coffee | 1 | 2015-01-25 00:00:00 | 11318.022050 | 1698.625640 |
| qux | 1 | 2015-01-25 00:00:00 | 8692.237650 | 1698.625640 |
| budgie | 1 | 2015-01-26 00:00:00 | 12117.415700 | 968.716570 |
| budgie | 1 | 2015-01-27 00:00:00 | 12117.415700 | 634.607040 |
| quux | 1 | 2015-01-27 00:00:00 | 15543.947200 | 634.607040 |
| qux | 1 | 2015-01-27 00:00:00 | 8692.237650 | 634.607040 |
| coffee | 1 | 2015-01-28 00:00:00 | 11318.022050 | 1555.946450 |
| quux | 1 | 2015-01-28 00:00:00 | 15543.947200 | 1555.946450 |
| bar | 1 | 2015-01-29 00:00:00 | 6224.922990 | 458.901930 |
| foo | 1 | 2015-01-29 00:00:00 | 9499.713070 | 458.901930 |
| quux | 1 | 2015-01-30 00:00:00 | 15543.947200 | 209.831860 |
| quux | 1 | 2015-01-31 00:00:00 | 15543.947200 | 476.337190 |
| bar | 1 | 2015-02-01 00:00:00 | 6224.922990 | 996.353310 |
| baz | 1 | 2015-02-01 00:00:00 | 15580.485020 | 996.353310 |
| budgie | 1 | 2015-02-01 00:00:00 | 12117.415700 | 996.353310 |
| quux | 1 | 2015-02-02 00:00:00 | 15543.947200 | 348.768090 |
| bar | 1 | 2015-02-03 00:00:00 | 6224.922990 | null |
| coffee | 1 | 2015-02-05 00:00:00 | 11318.022050 | 571.148730 |
| bar | 1 | 2015-02-06 00:00:00 | 6224.922990 | 1808.248080 |
| foo | 1 | 2015-02-06 00:00:00 | 9499.713070 | 1808.248080 |
| quux | 1 | 2015-02-06 00:00:00 | 15543.947200 | 1808.248080 |
| bar | 1 | 2015-02-07 00:00:00 | 6224.922990 | 413.590980 |
| baz | 1 | 2015-02-07 00:00:00 | 15580.485020 | 413.590980 |
| baz | 1 | 2015-02-08 00:00:00 | 15580.485020 | 1690.293980 |
| foo | 1 | 2015-02-08 00:00:00 | 9499.713070 | 1690.293980 |
| qux | 1 | 2015-02-08 00:00:00 | 8692.237650 | 1690.293980 |
| baz | 1 | 2015-02-09 00:00:00 | 15580.485020 | 1152.413020 |
| budgie | 1 | 2015-02-09 00:00:00 | 12117.415700 | 1152.413020 |
| coffee | 1 | 2015-02-10 00:00:00 | 11318.022050 | 913.641180 |
| baz | 1 | 2015-02-11 00:00:00 | 15580.485020 | 377.504700 |
| coffee | 1 | 2015-02-13 00:00:00 | 11318.022050 | 1073.617000 |
| qux | 1 | 2015-02-13 00:00:00 | 8692.237650 | 1073.617000 |
| baz | 1 | 2015-02-14 00:00:00 | 15580.485020 | 940.187570 |
| baz | 1 | 2015-02-15 00:00:00 | 15580.485020 | 324.303680 |
| baz | 1 | 2015-02-16 00:00:00 | 15580.485020 | 1520.799690 |
| budgie | 1 | 2015-02-16 00:00:00 | 12117.415700 | 1520.799690 |
| qux | 1 | 2015-02-17 00:00:00 | 8692.237650 | 161.219360 |
| budgie | 1 | 2015-02-18 00:00:00 | 12117.415700 | 441.257960 |
| quux | 1 | 2015-02-18 00:00:00 | 15543.947200 | 441.257960 |
| foo | 1 | 2015-02-19 00:00:00 | 9499.713070 | 796.852410 |
| baz | 1 | 2015-02-20 00:00:00 | 15580.485020 | 2220.330190 |
| coffee | 1 | 2015-02-20 00:00:00 | 11318.022050 | 2220.330190 |
| foo | 1 | 2015-02-20 00:00:00 | 9499.713070 | 2220.330190 |
| quux | 1 | 2015-02-20 00:00:00 | 15543.947200 | 2220.330190 |
| coffee | 1 | 2015-02-22 00:00:00 | 11318.022050 | 465.508770 |
| coffee | 1 | 2015-02-23 00:00:00 | 11318.022050 | 213.024870 |
| budgie | 1 | 2015-02-24 00:00:00 | 12117.415700 | null |
| qux | 1 | 2015-02-25 00:00:00 | 8692.237650 | 622.379370 |
| baz | 1 | 2015-02-26 00:00:00 | 15580.485020 | 2400.198700 |
| coffee | 1 | 2015-02-26 00:00:00 | 11318.022050 | 2400.198700 |
| foo | 1 | 2015-02-26 00:00:00 | 9499.713070 | 2400.198700 |
| qux | 1 | 2015-02-26 00:00:00 | 8692.237650 | 2400.198700 |
| bar | 1 | 2015-02-27 00:00:00 | 6224.922990 | 621.441300 |
| coffee | 1 | 2015-03-01 00:00:00 | 11318.022050 | 306.508530 |
| qux | 1 | 2015-03-02 00:00:00 | 8692.237650 | 965.593940 |
| qux | 1 | 2015-03-03 00:00:00 | 8692.237650 | 184.732280 |
| bar | 1 | 2015-03-04 00:00:00 | 6224.922990 | 1918.101030 |
| coffee | 1 | 2015-03-04 00:00:00 | 11318.022050 | 1918.101030 |
| qux | 1 | 2015-03-04 00:00:00 | 8692.237650 | 1918.101030 |
| baz | 1 | 2015-03-05 00:00:00 | 15580.485020 | 1001.228820 |
| foo | 1 | 2015-03-06 00:00:00 | 9499.713070 | 616.839970 |
| bar | 1 | 2015-03-07 00:00:00 | 6224.922990 | 541.216030 |
| quux | 1 | 2015-03-07 00:00:00 | 15543.947200 | 541.216030 |
| budgie | 1 | 2015-03-08 00:00:00 | 12117.415700 | 354.241740 |
| coffee | 1 | 2015-03-10 00:00:00 | 11318.022050 | 229.352060 |
| budgie | 1 | 2015-03-11 00:00:00 | 12117.415700 | 91.945190 |
| coffee | 1 | 2015-03-11 00:00:00 | 11318.022050 | 91.945190 |
| foo | 1 | 2015-03-12 00:00:00 | 9499.713070 | 629.308740 |
| quux | 1 | 2015-03-12 00:00:00 | 15543.947200 | 629.308740 |
| budgie | 1 | 2015-03-13 00:00:00 | 12117.415700 | 985.443300 |
| quux | 1 | 2015-03-13 00:00:00 | 15543.947200 | 985.443300 |
| quux | 1 | 2015-03-14 00:00:00 | 15543.947200 | 1401.613950 |
| qux | 1 | 2015-03-14 00:00:00 | 8692.237650 | 1401.613950 |
| baz | 1 | 2015-03-15 00:00:00 | 15580.485020 | 583.735120 |
| coffee | 1 | 2015-03-15 00:00:00 | 11318.022050 | 583.735120 |
| foo | 1 | 2015-03-16 00:00:00 | 9499.713070 | null |
| budgie | 1 | 2015-03-19 00:00:00 | 12117.415700 | 678.858540 |
| foo | 1 | 2015-03-19 00:00:00 | 9499.713070 | 678.858540 |
| bar | 1 | 2015-03-20 00:00:00 | 6224.922990 | 726.706800 |
| coffee | 1 | 2015-03-20 00:00:00 | 11318.022050 | 726.706800 |
| bar | 1 | 2015-03-21 00:00:00 | 6224.922990 | 908.055060 |
| foo | 1 | 2015-03-21 00:00:00 | 9499.713070 | 908.055060 |
| baz | 1 | 2015-03-22 00:00:00 | 15580.485020 | 2451.964490 |
| coffee | 1 | 2015-03-22 00:00:00 | 11318.022050 | 2451.964490 |
| quux | 1 | 2015-03-22 00:00:00 | 15543.947200 | 2451.964490 |
| qux | 1 | 2015-03-23 00:00:00 | 8692.237650 | 120.296260 |
| budgie | 1 | 2015-03-24 00:00:00 | 12117.415700 | 631.868340 |
| quux | 1 | 2015-03-24 00:00:00 | 15543.947200 | 631.868340 |
| coffee | 1 | 2015-03-25 00:00:00 | 11318.022050 | 659.141450 |
| quux | 1 | 2015-03-27 00:00:00 | 15543.947200 | 389.253460 |
| coffee | 1 | 2015-03-28 00:00:00 | 11318.022050 | 888.506310 |
| bar | 1 | 2015-03-29 00:00:00 | 6224.922990 | 656.975630 |
| foo | 1 | 2015-03-30 00:00:00 | 9499.713070 | 319.001630 |
| qux | 1 | 2015-03-30 00:00:00 | 8692.237650 | 319.001630 |
| baz | 1 | 2015-03-31 00:00:00 | 15580.485020 | 1216.804060 |
| quux | 1 | 2015-03-31 00:00:00 | 15543.947200 | 1216.804060 |
| quux | 1 | 2015-04-03 00:00:00 | 15543.947200 | 286.252680 |
| baz | 1 | 2015-04-04 00:00:00 | 15580.485020 | 600.597360 |
| budgie | 1 | 2015-04-06 00:00:00 | 12117.415700 | 727.247940 |
| quux | 1 | 2015-04-06 00:00:00 | 15543.947200 | 727.247940 |
| baz | 1 | 2015-04-07 00:00:00 | 15580.485020 | 489.016260 |
| bar | 1 | 2015-04-08 00:00:00 | 6224.922990 | 941.664260 |
| qux | 1 | 2015-04-08 00:00:00 | 8692.237650 | 941.664260 |
| foo | 1 | 2015-04-10 00:00:00 | 9499.713070 | 1042.039390 |
| quux | 1 | 2015-04-10 00:00:00 | 15543.947200 | 1042.039390 |
| quux | 1 | 2015-04-11 00:00:00 | 15543.947200 | 1451.050870 |
| qux | 1 | 2015-04-11 00:00:00 | 8692.237650 | 1451.050870 |
| coffee | 1 | 2015-04-13 00:00:00 | 11318.022050 | 1124.489320 |
| foo | 1 | 2015-04-13 00:00:00 | 9499.713070 | 1124.489320 |
| bar | 1 | 2015-04-14 00:00:00 | 6224.922990 | 720.692800 |
| budgie | 1 | 2015-04-14 00:00:00 | 12117.415700 | 720.692800 |
| budgie | 1 | 2015-04-16 00:00:00 | 12117.415700 | 375.031780 |
| bar | 1 | 2015-04-17 00:00:00 | 6224.922990 | 719.802760 |
| baz | 1 | 2015-04-17 00:00:00 | 15580.485020 | 719.802760 |
| coffee | 1 | 2015-04-18 00:00:00 | 11318.022050 | 1436.532380 |
| foo | 1 | 2015-04-18 00:00:00 | 9499.713070 | 1436.532380 |
| quux | 1 | 2015-04-19 00:00:00 | 15543.947200 | 692.414110 |
| budgie | 1 | 2015-04-21 00:00:00 | 12117.415700 | 835.678890 |
| qux | 1 | 2015-04-21 00:00:00 | 8692.237650 | 835.678890 |
| baz | 1 | 2015-04-22 00:00:00 | 15580.485020 | 854.115150 |
| quux | 1 | 2015-04-22 00:00:00 | 15543.947200 | 854.115150 |
| bar | 1 | 2015-04-24 00:00:00 | 6224.922990 | 384.977630 |
| budgie | 1 | 2015-04-24 00:00:00 | 12117.415700 | 384.977630 |
| budgie | 1 | 2015-04-25 00:00:00 | 12117.415700 | 1413.477800 |
| coffee | 1 | 2015-04-25 00:00:00 | 11318.022050 | 1413.477800 |
| quux | 1 | 2015-04-25 00:00:00 | 15543.947200 | 1413.477800 |
| budgie | 1 | 2015-04-26 00:00:00 | 12117.415700 | 658.975240 |
| coffee | 1 | 2015-04-26 00:00:00 | 11318.022050 | 658.975240 |
| baz | 1 | 2015-04-27 00:00:00 | 15580.485020 | 1259.157640 |
| budgie | 1 | 2015-04-27 00:00:00 | 12117.415700 | 1259.157640 |
| foo | 1 | 2015-04-28 00:00:00 | 9499.713070 | null |
| bar | 1 | 2015-04-29 00:00:00 | 6224.922990 | 687.214570 |
| foo | 1 | 2015-04-29 00:00:00 | 9499.713070 | 687.214570 |
| budgie | 1 | 2015-04-30 00:00:00 | 12117.415700 | 1200.455100 |
| qux | 1 | 2015-04-30 00:00:00 | 8692.237650 | 1200.455100 |
More Snippets
Used Versions
PHP 8.2, Laminas MVC 3.2, Symfony 5.2, Laravel 8.28, PHPUnit 9.5, Doctrine ORM 2.8