How to addition result of two queries?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
antubis
Forum Newbie
Posts: 16
Joined: Sun Apr 30, 2006 1:48 pm
Location: Pavlikeni, Bulgaria
Contact:

How to addition result of two queries?

Post 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!
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Re: How to addition result of two queries?

Post 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 ....
antubis
Forum Newbie
Posts: 16
Joined: Sun Apr 30, 2006 1:48 pm
Location: Pavlikeni, Bulgaria
Contact:

Post 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;
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
antubis
Forum Newbie
Posts: 16
Joined: Sun Apr 30, 2006 1:48 pm
Location: Pavlikeni, Bulgaria
Contact:

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: How to addition result of two queries?

Post 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";
angus
Forum Newbie
Posts: 10
Joined: Sat Sep 17, 2005 5:43 pm

Post 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;
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
antubis
Forum Newbie
Posts: 16
Joined: Sun Apr 30, 2006 1:48 pm
Location: Pavlikeni, Bulgaria
Contact:

Post by antubis »

Thanks to all.
The Everah decision was the right one.

Thanks to all again.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Glad I could help.
Post Reply