Home > Archive > PHP SQL > July 2005 > sum from two tables - one table, one query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
sum from two tables - one table, one query
|
|
|
| I have two tables:
table A
Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13
table B
Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6
How can I get sum of unique keys from table A and B (id_result) like this?:
id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11
| |
| Hilarion 2005-07-28, 4:00 am |
| >I have two tables:
>
> table A
>
> Id. | id_result | value
> 1 | 1| 10
> 2 | 1| 11
> 3 | 2| 7
> 4 | 2| 13
>
> table B
>
> Id. | id_result | value
> 1 | 1| 4
> 2 | 1| 1
> 3 | 2| 5
> 4 | 2| 6
>
>
> How can I get sum of unique keys from table A and B (id_result) like this?:
>
> id_result | sum_table_A | sum_table_B
> 1 | 21| 5
> 2 | 20| 11
Something like that should work (not tested):
SELECT
IFNULL( a.id_result, b.id_result ) id_result,
SUM( a.value ) sum_table_A,
SUM( b.value ) sum_table_B
FROM table_A a FULL OUTER JOIN table_B b ON a.id_result = b.id_result
GROUP BY IFNULL( a.id_result, b.id_result )
ORDER BY IFNULL( a.id_result, b.id_result )
Hilarion
| |
| Bob Stearns 2005-07-28, 5:01 pm |
| Piotr wrote:
> I have two tables:
>
> table A
>
> Id. | id_result | value
> 1 | 1| 10
> 2 | 1| 11
> 3 | 2| 7
> 4 | 2| 13
>
> table B
>
> Id. | id_result | value
> 1 | 1| 4
> 2 | 1| 1
> 3 | 2| 5
> 4 | 2| 6
>
>
> How can I get sum of unique keys from table A and B (id_result) like this?:
>
> id_result | sum_table_A | sum_table_B
> 1 | 21| 5
> 2 | 20| 11
Untested, but something like:
select ta.id_result, sum(ta.value), sum(tb.value)
from table_a ta
join table_b tb on ta.result_id=tb.result_id
order by ta.id_result
Should be close to the result you want, if you are interested only in
result_ids that are in both tables, rather than in in just one.
| |
|
| Bob Stearns napisał(a):
> Untested, but something like:
>
> select ta.id_result, sum(ta.value), sum(tb.value)
> from table_a ta
> join table_b tb on ta.result_id=tb.result_id
> order by ta.id_result
>
> Should be close to the result you want, if you are interested only in
> result_ids that are in both tables, rather than in in just one.
Thank You both. I will try to remake it. Now it don't work on mySQL
|
|
|
|
|