Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Okay, I believe I understand what's happening.</p> <p>So, presumably either table can have more than one record per login_id, which is why you're doing the sum and group by. However, take a look at what your query gives you back when you take out the aggregates and the group by and I think you may understand the problem.</p> <p>Let's say you have table 1 which has 2 records for login_id xxx1234 and table 2 which has 3 records for that login_id. Then when you left join them, you will get <strong>6 records</strong>.</p> <p>e.g.</p> <pre><code>table 1: login, custotal xxx111, 10 xxx111, 20 table 2: login, amount xxx111, 30 xxx111, 40 xxx111, 50 after the join: login, custotal, amount xxx111 10, 30 xxx111 10, 40 xxx111 10, 50 xxx111 20, 30 xxx111 20, 40 xxx111 20, 50 </code></pre> <p>So you will be potentially getting several times the amounts you expected in your sums.</p> <p>What you instead want to do is something like this:</p> <pre><code> SELECT billing_totals.login_id, billing_totals.primary_key, billing_totals.a_name, billing_totals.finaltotal, billing_dates_totals.paidtotal, (billing_totals.finaltotal - billing_dates_totals.paidtotal) AS total FROM (SELECT $mysql_billing.login_id, $mysql_billing.primary_key, $mysql_billing.a_name, SUM($mysql_billing.custotal) AS finaltotal FROM $mysql_billing GROUP BY login_id) billing_totals LEFT JOIN (SELECT $mysql_billing_dates.login_id, SUM($mysql_billing_dates.amount) AS paidtotal FROM $mysql_billing_dates GROUP BY login_id) billing_dates_totals ON (billing_totals.login_id = billing_dates_totals.login_id) ORDER BY a_name ASC"; </code></pre> <p>Probably need to tweak that slightly as I don't have a mysql to test it on at the moment.</p>
    singulars
    1. This table or related slice is empty.
    1. POmysql query on 2 tables with sum
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFoolishSeth
    1. USFoolishSeth
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POmysql query on 2 tables with sum
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COi want it to look in the db and pull each invoice, add up the custotal and hold it for a calculation to subtract from the sum from another table field.
      singulars
      1. PO
      1. USuser1710960
    2. COUpdated the answer above. Your join is giving you more records than you think, so your sums are wrong.
      singulars
      1. PO
      1. USFoolishSeth
    3. COyou are absolutely correct. I did tweak it and it works as needed. While I study how you came up with this, where would I put a where clause such as 'invoicedate <= 20120515' - by the way, you are awesome, I would've never figured this one out. Thanks a million!
      singulars
      1. PO
      1. USuser1710960
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload