Jump to content
I-AM-OBODO

Sum row in UNION

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

Share this post


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.

Share this post


Link to post
Share on other sites

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.

Share this post


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

Share this post


Link to post
Share on other sites

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.