Page 1 of 1

summing column from 3 different tables

Posted: Fri Feb 08, 2013 2:31 am
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!

Re: summing column from 3 different tables

Posted: Fri Feb 08, 2013 1:56 pm
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'

Re: summing column from 3 different tables

Posted: Fri Feb 08, 2013 10:56 pm
by japeth
got an error

it says. "Column 'days' in field list is ambiguous"

Re: summing column from 3 different tables

Posted: Sun Feb 10, 2013 11:11 pm
by japeth
I'm running out of Ideas. sad to say.

Re: summing column from 3 different tables

Posted: Mon Feb 11, 2013 12:48 am
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,