Could anyone point out where my sql query is wrong, it doesn't give the desired result regarding the SUM when i fetch the data?
I am trying to calculate the first 5 VAR_MEASURE_1 results of each union, for the first union i should be getting -7.00 but I'm getting 4.00.
EDIT - here is the data before I use the sum query-
VAR_MEASURE_1
16587 SBEN -14.000 0.000 2014-09-30 00:31:24
16288 SBEN 3.000 0.000 2014-09-30 00:52:46
16288 SBEN 3.000 0.000 2014-09-30 00:53:59
16006 SBEN 5.000 0.000 2014-09-30 01:16:36
15271 SBEN -4.000 0.000 2014-09-30 01:40:09
15786 SBEN -6.000 0.000 2014-09-29 00:15:02
16097 SBEN 2.000 0.000 2014-09-29 00:17:34
16097 SBEN 2.000 0.000 2014-09-29 00:19:25
15771 SBEN -9.000 0.000 2014-09-29 00:38:49
16155 SBEN 7.000 0.000 2014-09-29 01:13:26
15661 SBEN -9.000 0.000 2014-09-28 17:40:05
14425 SBEN 1.000 0.000 2014-09-28 17:49:09
14425 SBEN 1.000 0.000 2014-09-28 17:50:24
15657 SBEN -9.000 0.000 2014-09-28 18:02:09
15655 SBEN -14.000 0.000 2014-09-28 18:24:31
Here is it after
SUMA
15276 Sample 1 SBEN 4.000 2014-09-30 00:31:24
15855 Sample 2 SBEN -77.000 2014-09-29 02:40:56
15661 Sample 3 SBEN -109.000 2014-09-28 17:40:05
$sql = "(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-30 00:00:00' AND '2014-09-30 23:59:59' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN' LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-29 00:00:01' AND '2014-09-29 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-28 00:00:01' AND '2014-09-28 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-27 00:00:01' AND '2014-09-27 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5)
UNION
(SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA FROM LIVE_VARCOLL WHERE doe between '2014-09-26 00:00:01' AND '2014-09-26 23:59:01' AND CONTRACT_CODE = 'DEC' AND VARIABLE = 'SBEN'
ORDER BY doe LIMIT 5);";
You're misintepretting the way of how SUM() works.
SUM() does not limit itself to LIMIT - LIMIT only tells sql how many rows you want to get in the result. So in your case each UNION part has the SUM of all rows fulfilling the requirements, not only the first five of them.
If you want to select the sum of the first five rows of a table, that meet certain conditions, you should do it like in the folllowing example. The subquery retrieves only five rows, and then the outer query is able to sum it up.
To be clear - this is the SQL to retrieve the data only of the first part of your UNIONed query. You could try and build your other UNION parts like that:
SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-30 00:00:00'
AND '2014-09-30 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp
So your UNIONs should look like this:
(SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-30 00:00:00'
AND '2014-09-30 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp)
UNION
( SELECT tmp.*, SUM(tmp.VAR_MEASURE_1) as SUMA
FROM (
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, CONTRACT_CODE
FROM LIVE_VARCOLL
WHERE doe between '2014-09-29 00:00:00'
AND '2014-09-29 23:59:59'
AND CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
ORDER BY doe
LIMIT 5
) tmp)
UNION
( .... more sqls here if needed ... )
But I assume you want to get the results by day, and if the "first five rows per day" is not important, you could simply go with selecting summed results grouped by day (which you achieve by DATE(doe)):
SELECT ID, VARIABLE, VAR_MEASURE_1, doe, DATE(doe) as date_doe, CONTRACT_CODE, SUM(VAR_MEASURE_1) AS SUMA
FROM LIVE_VARCOLL
WHERE CONTRACT_CODE = 'DEC'
AND VARIABLE = 'SBEN'
GROUP BY DATE(doe)
ORDER BY DATE(doe)