SUM无法使用UNION - MYSQL正确计算

Question

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);";

Answer 1

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)