In each table have many number of rows with amount. Like this i have different numbers of amount in each table. Each tables field name is different. How can i get the total of all values in the four tables in a single query? Any way is there?
+2
A:
Did you try something like this?
select sum(val) from (
select sum(col1) as val from table1 where criteria1 = 'x'
union all
select sum(col2) from table2 where criteria2 = 'y'
union all
select sum(col3) from table3 where criteria3 = 'z'
union all
select sum(col4) from table4 where criteria4 = 'w'
) newTbl
MJB
2010-04-15 13:41:57
In some databases the union command automatically removes duplicate rows. This would be a problem if you get the same sum from two of the tables. Use 'union all' to preserve duplicates.
BenV
2010-04-15 13:47:38
Good point. Edited my answer.
MJB
2010-04-15 13:49:22
I used this way, i got this type of error. #1248 - Every derived table must have its own alias Y it is so ? and one more thing 'union all' is working in mysql?
Karthik
2010-04-15 13:52:12
SELECT sum( val )FROM (SELECT sum( rtotal ) AS valFROM WWHERE rrdate = '2010-04-15'AND active = '1'UNION SELECT sum( ertotal )FROM XWHERE errdate = '2010-04-15'AND active = '1'UNION SELECT sum( crtotal )FROM YWHERE crrdate = '2010-04-15'AND active = '1'UNION SELECT sum( iamount )FROM ZWHERE idate = '2010-04-15'AND ifor = 'R')MySQL said: Documentation#1248 - Every derived table must have its own alias This is the error i got
Karthik
2010-04-15 14:01:26
edited answer -- notice the alias at the end.
MJB
2010-04-15 14:06:45
Oh ok thanks very much. Now it worked fine. Thanks a lot
Karthik
2010-04-15 14:16:43
A:
use derived tables - u probably want to do something like this so you get all results in a single row !
select
u.user_count,
c.country_count
from
(
select count(*) as user_count from users where username like 'f%'
) u
join
(
select count(*) as country_count from country
) c;
a more comlpex example: http://pastie.org/921407
f00
2010-04-15 16:00:06