summing column from 3 different tables

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
japeth
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

summing column from 3 different tables

Post by japeth »

Helo Guys,

I'm new member here. I just thought Google is enough to solve my problem regarding mysql.

can someone help me out to solve this.

I have 3 tables.

Tbl_1
id-------emID-------days

Tbl_2
id-------emID-------days

Tbl_3
id-------emID-------days

I have script like this.

Code: Select all

SELECT SUM(days) AS total_days
		FROM (SELECT `days` FROM `Tbl_1`  WHERE `emID`=0001
		UNION ALL
		SELECT `days` FROM `Tbl_2`  WHERE `emID`=0001
                UNION ALL
		SELECT `days` FROM `Tbl_3`  WHERE `emID`=0001) t
I want to sum all (days) column from my 3 tables with where clause. how should i do that?

All best!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: summing column from 3 different tables

Post by Christopher »

So this does not work?

Code: Select all

SELECT SUM(days) AS total_days
		FROM (SELECT `days` FROM `Tbl_1`  WHERE `emID`=0001
		UNION ALL
		SELECT `days` FROM `Tbl_2`  WHERE `emID`=0001
                UNION ALL
		SELECT `days` FROM `Tbl_3`  WHERE `emID`=0001) t
Did you try just:

Code: Select all

SELECT SUM(days) AS total_days FROM `Tbl_1` JOIN `Tbl_2`  JOIN `Tbl_3`  WHERE `emID`='0001'
(#10850)
japeth
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

Re: summing column from 3 different tables

Post by japeth »

got an error

it says. "Column 'days' in field list is ambiguous"
japeth
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

Re: summing column from 3 different tables

Post by japeth »

I'm running out of Ideas. sad to say.
japeth
Forum Newbie
Posts: 12
Joined: Fri Feb 08, 2013 2:21 am

Re: summing column from 3 different tables

Post by japeth »

To those,

I found a solution

SELECT SUM(column) as totaldays
FROM (
SELECT * FROM `tbl1` WHERE `column`='column_val'
UNION ALL
SELECT * FROM `tbl2` WHERE `column`='column_val'
UNION ALL
SELECT * FROM `tbl3` WHERE `column`='column_val'
) AS somealias


Thanks,
Post Reply