Php, MySql Sum from two tables

Question

I'm trying to make this query work:

SELECT 
  IFNULL(SUM(days), 0) AS days 
FROM
  `table_days` 
WHERE task = 1 
GROUP BY task 
UNION
ALL 
SELECT 
  IFNULL(SUM(total), 0) AS total 
FROM
  `table_total` 
WHERE task = 1 
GROUP BY task ;

I have two tables :

1. table_days

    id      task    days    
    ==========================
    1       1       3.00
    2       1       2.00


2. table_total
    id      task    total   
    ==========================
    1       3       0.00

The query above partially works, the result is:

stdClass Object
(
    [days] => 5.00
)

but I would like to get the result from second table even if there are no records found. Something like

stdClass Object
(
    [days] => 5.00
    [total] => 0.00
)

Vipin Jain · Accepted Answer · 2016-03-22 05:09:26Z

1

Try This query

SELECT 
  IFNULL(SUM(days), 0) AS days 
FROM
  `table_days` 
WHERE task = 1 
GROUP BY task 
UNION
ALL 
SELECT 
  SUM(case when task = 1 then IFNULL(total,0) else 0 end) AS total 
FROM
  `table_total` 
GROUP BY task ;

answered Mar 22, 2016 at 5:09

Vipin Jain

3,77618 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alko Over a year ago

I tried your query, and produces exactly the same result as mine, (total value is not shown)

alexander.polomodov · Accepted Answer · 2016-03-22 05:34:18Z

0

This code will work for you (example at sqlfiddle):

SELECT
  IFNULL(SUM(days), 0) AS value, 'days' as name
FROM
  `table_days`
WHERE task = 1
GROUP BY task
UNION ALL

(SELECT CASE WHEN u.value = -1 then 0 else value end as value, 'total' as name
    FROM
    (
        SELECT
          IFNULL(SUM(total), 0) AS value
        FROM
          `table_total`
        WHERE task = 1
        GROUP BY task
        UNION
        SELECT -1 as value
    ) u
);

And it will return:

+-------+-------+
| value | name  |
+-------+-------+
|     5 | days  |
|     0 | total |
+-------+-------+

edited Mar 22, 2016 at 5:34

answered Mar 22, 2016 at 5:12

alexander.polomodov

5,54414 gold badges44 silver badges48 bronze badges

3 Comments

Alko Over a year ago

task-3 is purposely left there, so if there are no records, I sill want to return 0

alexander.polomodov Over a year ago

ok, i get it. I fix code and now it works for task=1 in second select

Alko Over a year ago

Still not working. This is what I'm getting: [value] => 5.000 [name] => days

Tim Biegeleisen · Accepted Answer · 2016-03-22 05:15:02Z

0

The conundrum with your current approach is that the second half of your UNION query doesn't "know" anything about the tasks which appear in the first half. However, joining the two tables would allow you to leverage the relationship between the two tables. I think a better way of doing this would be to LEFT JOIN the table_days table with table_total, thereby retaining all tasks:

SELECT t1.task, t1.sum_days, t2.sum_total
FROM
(
    SELECT task, IFNULL(SUM(days), 0) AS sum_days
    FROM `table_days`
    GROUP BY task
) t1
LEFT JOIN
(
    SELECT task, IFNULL(SUM(total), 0) AS sum_total
    FROM `table_total`
    GROUP BY task
) t2
    ON t1.task = t2.task
WHERE t1.task = 1

If you want to include all tasks from the table_days table, you can remove the WHERE clause.

edited Mar 22, 2016 at 5:15

answered Mar 22, 2016 at 5:08

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Pallavi Over a year ago

Your query gives Error Code : 1054 Unknown column 'days' in 'field list'. Please update second select statement IFNULL(SUM(days), 0) with IFNULL(SUM(total), 0)

Tim Biegeleisen Over a year ago

@Pallavi Sorry that should have been the total column, please try it again.

Collectives™ on Stack Overflow

Php, MySql Sum from two tables

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related