How would you use SUM twice in a single query from two separate tables?

[imgrooot]

So I am trying to retrieve earnings of a campaign from two different tables and order them from highest to lowest.  

 

It works fine if I do it from a single table "member_earnings". 

$get_raised = $db->prepare("SELECT SUM(member_earnings.deposit) AS total_m, campaigns.campaign_id FROM campaigns
INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id
WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total_m DESC");
$get_raised->bindValue(':campaign_id', 0);
$get_raised->execute();
$result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
if(count($result_raised) > 0) {
  foreach($result_raised as $row) {
    $total_m = $row['total_m'];
    
    $total_earnings = $total_m;
  }
}

Here is my code when add another table "guest earnings". I don't get any errors. But the amount I receive from guest earnings quadruples the original amount.  Also in this scenario, how would I ORDER BY considering I am retrieving numbers from two different tables? I can't use "total_m" or "total_g". Anyway to combine the two?

$get_raised = $db->prepare("SELECT SUM(member_earnings.deposit) AS total_m, SUM(guest_earnings.deposit) AS total_g, campaigns.campaign_id FROM campaigns
INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id
INNER JOIN guest_earnings ON campaigns.campaign_id = guest_earnings.campaign_id
WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total_m DESC");
$get_raised->bindValue(':campaign_id', 0);
$get_raised->execute();
$result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
if(count($result_raised) > 0) {
  foreach($result_raised as $row) {
    $total_m = $row['total_m'];
    $total_g = $row['total_g'];
    
    $total_earnings = $total_m + $total_g;
  }
}

Edited May 5, 2018 by imgrooot

[Barand]

I see you are still doing the redundant "WHERE id > 0".

 

If a record in one table matches 4 records then you will get values in that second table added 4 times. The way around it is to use a subquery to get the totals from the second table and join to that subquery. Something like this

SELECT a.id
     , SUM(a.amount) as tot_a
     , b.total as tot_b
FROM tableA a
     JOIN (
          SELECT id
               , SUM(amount) as total
          FROM tableB
          GROUP BY id
          ) btots  USING (id)
GROUP BY id 

[imgrooot]

 

I see you are still doing the redundant "WHERE id > 0".

 

If a record in one table matches 4 records then you will get values in that second table added 4 times. The way around it is to use a subquery to get the totals from the second table and join to that subquery. Something like this

SELECT a.id
     , SUM(a.amount) as tot_a
     , b.total as tot_b
FROM tableA a
     JOIN (
          SELECT id
               , SUM(amount) as total
          FROM tableB
          GROUP BY id
          ) btots  USING (id)
GROUP BY id 

 

I meant to have it WHERE id > 1. I didn't want to show the first record.

 

As per your example, I tried my best to replicate it using my query but so far no success.  

 

Here's the new query. Can you please fix what's wrong with it?

$get_raised = $db->prepare("SELECT member_earnings.campaign_id, SUM(member_earnings.deposit) AS total_e, guest_earnings.deposit AS total_g FROM campaigns
INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id
JOIN (SELECT campaign_id, SUM(deposit) as total FROM guest_earnings GROUP BY campaign_id) btots USING (campaign_id)
WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY btots DESC");
$get_raised->bindValue(':campaign_id', 1);
$get_raised->execute();
$result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
if(count($result_raised) > 0) {
  foreach($result_raised as $row) {
    $total_m     = $row['total_m'];
    $total_g     = $row['total_g'];
    $grand_total = $row['btots'];
    
    $total_earnings = $grand_total;
  }
}

[mac_gyver]

i would store all the earnings in one table, with a member/guest 'type' column (or perhaps you are already storing a value that distinguishes between members and guests?)

 

if you only want the total per campaign, the query structure you already have would produce the correct result.

 

if you also want to retrieve and display separate earning values for members and guests, you would just add conditional logic in the SELECT term to SUM() the two 'type' values separately.

 

if at any point you just want to query for member earnings or for guest earnings, rather than to query against a separate table for each, you would just add the appropriate condition to the WHERE term.

Edited May 6, 2018 by mac_gyver

[Barand]

SELECT ... , guest_earnings.deposit AS total_g ...

should be

SELECT ... , btots.total as total_g ...

Your earnings table has now been condensed into a table subquery (alias btots)

 

(Mea culpa - I should have put btots.total in my example)

[imgrooot]

SELECT ... , guest_earnings.deposit AS total_g ...

should be

SELECT ... , btots.total as total_g ...

Your earnings table has now been condensed into a table subquery (alias btots)

 

(Mea culpa - I should have put btots.total in my example)

 

 

Was away for few days. I'm back now.

 

I changed it to what you wrote but still get the same error as before. Which is this.

Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'campaign_id' in from clause is ambiguous in 

[imgrooot]

i would store all the earnings in one table, with a member/guest 'type' column (or perhaps you are already storing a value that distinguishes between members and guests?)

 

if you only want the total per campaign, the query structure you already have would produce the correct result.

 

if you also want to retrieve and display separate earning values for members and guests, you would just add conditional logic in the SELECT term to SUM() the two 'type' values separately.

 

if at any point you just want to query for member earnings or for guest earnings, rather than to query against a separate table for each, you would just add the appropriate condition to the WHERE term.

 

Unfortunately I have to keep the two earning types as separate. Otherwise it would be a very messy table. 

 

I can retrieve the individual earnings and combine them for each campaign listed. But the issue I have is that I need to be able to sort them from highest to lowest amount. 

 

This is a more simpler code but I have no clue on sorting them from highest to lowest, aside from from the method "Barand" mentioned.

$find_campaign = $db->prepare("SELECT campaigns.campaign_id, member_earnings.deposit, guest_earnings.deposit FROM campaigns
LEFT JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id
LEFT JOIN guest_earnings ON campaigns.campaign_id = guest_earnings.campaign_id
GROUP BY campaigns.campaign_id ORDER BY campaigns.campaign_id DESC LIMIT 20");
$find_campaign->execute();
$result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find_campaign) > 0) {
  foreach($result_find_campaign as $row) {
    $campaign_id           =	$row['campaign_id'];

        $get_raised_1 = $db->prepare("SELECT deposit FROM member_earnings WHERE campaign_id = :campaign_id");
  	$get_raised_1->bindParam(':campaign_id', $campaign_id);
  	$get_raised_1->execute();
  	$result_raised_1 = $get_raised_1->fetchAll(PDO::FETCH_ASSOC);
  	if(count($result_raised_1) > 0) {
  		$ub = 0;
  		foreach($result_raised_1 as $key=>$row) {
  		  $member_raised+= $row['deposit'];
  		}
  	}

        $get_raised_2 = $db->prepare("SELECT deposit FROM guest_earnings WHERE campaign_id = :campaign_id");
  	$get_raised_2->bindParam(':campaign_id', $campaign_id);
  	$get_raised_2->execute();
  	$result_raised_2 = $get_raised_2->fetchAll(PDO::FETCH_ASSOC);
  	if(count($result_raised_2) > 0) {
  		$ub = 0;
  		foreach($result_raised_2 as $key=>$row) {
  		  $guest_raised+= $row['deposit'];
  		}
  	}

    $campaign_raised = $member_raised + $guest_raised;

    echo $campaign_raised;
    
  }
}

Edited May 11, 2018 by imgrooot

[Barand]

You could try this alternative approach. (The subquery effectively puts the member and guest earnings into a single temporary table)

 

SELECT c.campaign_id
     , SUM(CASE etype WHEN 'M' THEN deposit ELSE 0 END) as member_earnings
     , SUM(CASE etype WHEN 'G' THEN deposit ELSE 0 END) as guest_earnings
     , SUM(deposit) as total
FROM
     campaigns c
     LEFT JOIN (
               SELECT campaign_id
                    , deposit
                    , 'M' as etype
               FROM member_earnings
               UNION
               SELECT campaign_id
                    , deposit
                    , 'G' as etype
               FROM guest_earnings
               ) earns USING (campaign_id)
GROUP BY c.campaign_id
ORDER BY total DESC

[imgrooot]

 

You could try this alternative approach. (The subquery effectively puts the member and guest earnings into a single temporary table)

SELECT c.campaign_id
     , SUM(CASE etype WHEN 'M' THEN deposit ELSE 0 END) as member_earnings
     , SUM(CASE etype WHEN 'G' THEN deposit ELSE 0 END) as guest_earnings
     , SUM(deposit) as total
FROM
     campaigns c
     LEFT JOIN (
               SELECT campaign_id
                    , deposit
                    , 'M' as etype
               FROM member_earnings
               UNION
               SELECT campaign_id
                    , deposit
                    , 'G' as etype
               FROM guest_earnings
               ) earns USING (campaign_id)
GROUP BY c.campaign_id
ORDER BY total DESC

 

Holy cow that actually worked. Thanks a million!!!