Sum row in UNION

[I-AM-OBODO]

Hello guys,

I'm try to sum rows in a UNION but having a hard time about it

$stmt = $pdo->prepare("SELECT due_date, SUM(amount_paid) FROM (

    SELECT due_date, amount_paid FROM table1 union all

    SELECT due_date, amount_paid FROM table2 UNION ALL

)x GROUP BY MONTH");

$stmt->execute();
 
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {  
echo $row['x'];

thanks

[Barand]

For a start, too many "UNIONS".

SELECT MONTH(due_date) as month
, SUM(amount_paid) as total
FROM (

    SELECT due_date, amount_paid FROM table1 
	UNION ALL
    SELECT due_date, amount_paid FROM table2 
	
    )x 
GROUP BY month

If you are having to do that, it looks like your table1 and table2 should be a single table with an additional identifier column.

[benanamen]

I agree with @Barand on the single table. Whenever I see UNION being used on a forum the DB design is always not properly normalized.

[I-AM-OBODO]

For a start, too many "UNIONS".

SELECT MONTH(due_date) as month
, SUM(amount_paid) as total
FROM (

    SELECT due_date, amount_paid FROM table1 
	UNION ALL
    SELECT due_date, amount_paid FROM table2 
	
    )x 
GROUP BY month

If you are having to do that, it looks like your table1 and table2 should be a single table with an additional identifier column.

 

I think i agree with you. I have changed the table to a singe table with an identifier. I created two tables cos of an issue but i have been able to sail pass that now.

thanks