Page 1 of 1

How to addition result of two queries?

Posted: Tue May 09, 2006 5:29 am
by antubis
Hello,

I want to addition the result from two diffrent tables.

First query is: SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10;
Second query is SELECT column FROM table2 WHERE column1 = 10;

With two diffrent queries + PHP the addition is very easy :), but I don`t wanna make two queries.
How can I do this with one single query?

Greetings!

Posted: Tue May 09, 2006 6:25 am
by dibyendrah
The query will look like :

Code: Select all

SELECT SUM(table1.column)  AS some_value, table2.colum FROM table1, table2 WHERE table1.column = table2.column AND table1.column =10 ; 
Cheers,
Dibyendra

Re: How to addition result of two queries?

Posted: Tue May 09, 2006 6:28 am
by raghavan20
antubis wrote:Hello,

I want to addition the result from two diffrent tables.

First query is: SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10;
Second query is SELECT column FROM table2 WHERE column1 = 10;

With two diffrent queries + PHP the addition is very easy :), but I don`t wanna make two queries.
How can I do this with one single query?

Greetings!
what do you mean by addition?
adding result sets?
or
sum?
or
concatenation?

write how your desired output should look like ....

Posted: Tue May 09, 2006 6:36 am
by antubis
If first query ( SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10; ) returns 5 and second query( SELECT column FROM table2 WHERE column1 = 10; ) returns 6 the result column should be 11;

I want to SUM the return values from two queries in one result by using single query;

Posted: Tue May 09, 2006 8:58 am
by raghavan20
antubis wrote:If first query ( SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10; ) returns 5 and second query( SELECT column FROM table2 WHERE column1 = 10; ) returns 6 the result column should be 11;

I want to SUM the return values from two queries in one result by using single query;

Code: Select all

SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10
UNION
SELECT column FROM table2 WHERE column1 = 10;
For union, you just have to make sure that the queries you are joining have the same number of columns.

by the way,,,the first query cannot return 5 rows since it uses a group by function.

Posted: Tue May 09, 2006 10:13 am
by timvw
It all depends on what your sql dbms supports... The simplest would be:

Code: Select all

SELECT (SELECT first) + (SELECT second) AS sum

Posted: Tue May 09, 2006 10:30 am
by antubis
No. This method doesn`t work in MySQL

I try even the simplest:
mysql> SELECT ( SELECT 1 + 1 ) + ( SELECT 2 + 2 ) AS sum;

Error:
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'SELECT 1 + 1
) + ( SELECT 2 + 2 ) AS sum' at line 1

Re: How to addition result of two queries?

Posted: Tue May 09, 2006 11:05 am
by RobertGonzalez
antubis wrote: First query is: SELECT SUM(column) AS some_value FROM table1 WHERE column1 = 10;
Second query is SELECT column FROM table2 WHERE column1 = 10;

Code: Select all

$sql = "SELECT ( SUM(f.column) + s.column ) FROM table1 f INNER JOIN table2 s ON f.column1 = s.column WHERE f.column1 = 10";

Posted: Tue May 09, 2006 11:44 am
by angus
Have you tried this?

SELECT (SELECT SUM(column) FROM table1 WHERE column1=10) + (SELECT SUM(column) FROM table2 WHERE column1=10) FROM table1, table2;

Posted: Tue May 09, 2006 12:34 pm
by timvw
You'll need to be more specific about the version of your MySQL server...

Code: Select all

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.20a-Debian_2-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table test1(count int not null, primary key(count));
Query OK, 0 rows affected (0.01 sec)

mysql> create table test2(count int not null, primary key(count));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values (100), (40);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into test2 values (60) , (100);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT (SELECT SUM(count) FROM test1) + (SELECT SUM(count) FROM test2) AS sum;
+------+
| sum  |
+------+
| 300  |
+------+
1 row in set (0.03 sec)

Posted: Tue May 09, 2006 2:09 pm
by antubis
Thanks to all.
The Everah decision was the right one.

Thanks to all again.

Posted: Tue May 09, 2006 2:29 pm
by RobertGonzalez
Glad I could help.