EXPLAIN EXTENDED

From Stack Overflow:

I would like to know the sum of all web services completions per hour of day.

Obviously, this can be easily done with SUM() and GROUP BY:

SELECT hour, SUM(calls) FROM sample s GROUP BY hour;  

hour	SUM(calls)
0	634
1	642
2	633
3	624
4	420
5	479
6	428
7	424
8	473
9	434
10	485
11	567
12	526
13	513
14	555
15	679
16	624
17	796
18	752
19	843
20	827
21	774
22	647
23	533

My problem is that in old sets, the web service calls in the hours [00-11] were already summed up.

The simple statement as listed above would therefore lead to

SELECT hour, SUM(calls) FROM sample s GROUP BY hour;  

hour	SUM(calls)
0	6234
12	526
13	513
14	555
15	679
16	624
17	796
18	752
19	843
20	827
21	774
22	647
23	533

This is an undesirable result. To make the old sets [00,12,...,23] comparable to the new sets [00,01,...,23], I would like to have one statement that averages the value of [00] and distributes it over the missing hours, e.g.:

hour	SUM(calls)
0	6243/12
1	6243/12
12	526
23	533

I can easily do this using temporary tables or views, but i don't know how to accomplish this without them.

In this rowset, we have a row that needs to be split into 12 another rows.
Read the rest of this entry »