Trouble With Select Statement That Combines 2 Columns From 2 Tables

[jbrack321]

Hello,

 

I'm having trouble with select statement that combines 2 columns from 2 tables (summing column2 for all similar states in column1).

 

1st table = tracker

column1 = state

column2 = data

 

example below:

state | datap

TX | 1000

----------------------------

 

2nd table = precaution

column1 = state

column2 = data

 

example below:

 

state | datap

TX | 100

LA | 10000

---------------------------

 

Result should be:

state | datap

TX | 1100

LA | 10000

 

I'm hung up on the following select statement (close but no cigar)

$strQuery = "SELECT distinct tracker.state, sum(tracker.data) datap FROM tracker group by state UNION ALL SELECT distinct precaution.state, sum(precaution.data) datap FROM precaution group by state";

 

Result of code above does not combine similar states and their related data

 

For example, code above does:

state | datap

TX | 100

TX | 1000

LA | 10000

 

 

Any help would be appreciated.

 

 

Thanks,

 

JB

 

[/size]

[natg504]

Try this:

 

SELECT tbl_union.state, SUM(tbl_union.data) AS total_Data
FROM
(SELECT DISTINCT tracker.state, tracker.data from tracker UNION ALL SELECT precaution.state, precaution.data from precaution) 
AS tbl_union
GROUP BY  tbl_union.state

[jbrack321]

That did the trick !!!!!

 

Thanks so much.

 

JB

[Sanjukta]

Hey,

 

Glad that your issue is resolved.

 

Happy FusionCharting!