Jump to content

Sum row in UNION


Go to solution Solved by Barand,

Recommended Posts

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

Link to post
Share on other sites
  • Solution

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.

Link to post
Share on other sites

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

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.