CODEWITHSUNDEEP
mysqlsumunion
NotGaeL
NotGaeL

Reputation: 8484

mysql SUM(column) won't sum elements on column union

Can anybody tell me why this code:

SELECT t.prob FROM 
(SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2) AS t
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2)
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2);

produces the same output as this instead of the sum of 'prob'?

SELECT SUM(t.prob) FROM 
(SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2) AS t
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2)
 UNION ALL (SELECT e1.evaled/IFNULL(NULLIF(e2.total,0),1) AS prob FROM 
    (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2);

(the code basically creates a column prob containing just one value for each of three tables els, els2 and els3 and then combines the three of them into just one column from which I want the sum of its 3 elements)

I came up with this other code. It works and it's a clearer statement, so nevermind:

SELECT SUM(t.evaled/IFNULL(NULLIF(t.total,0),1)) as sumatory FROM
(SELECT evaled,total FROM
 (SELECT COUNT(*) AS evaled FROM els WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els) AS e2
UNION ALL SELECT * FROM
 (SELECT COUNT(*) AS evaled FROM els2 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els2) AS e2
UNION ALL SELECT * FROM
 (SELECT COUNT(*) AS evaled FROM els3 WHERE evals=0) AS e1 
     INNER JOIN (SELECT COUNT(*) AS total FROM els3) AS e2) as t;

Upvotes: 0

Views: 189

Answers (1)

barsju
barsju

Reputation: 4446

Not sure what goes wrong in your solution but I think I get what your are trying to achieve. How about something like this approach:

SELECT 
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els) AS evaled FROM els WHERE evals=0)
 +
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els2) AS evaled FROM els2 WHERE evals=0) 
 +
 (SELECT COUNT(*)/(SELECT COUNT(*) FROM els3) AS evaled FROM els3 WHERE evals=0)
 /3

Edit: And if you want average I guess you need a /3 at the end..

Upvotes: 1

Related Questions