Databases
StackOverflow2013
Postsdbo
POSum of All Related Rows with Matching ID MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSum of All Related Rows with Matching ID MySQL
    primarykey
    data
    text
    <p>I have the following table schema:</p> <pre><code> tbl_portfolio ---------- id (auto number) name </code></pre> <p>-</p> <pre><code> tbl_registration ---------------- id(auto number) name portfolio_id (FK to tbl_portfolio.id) </code></pre> <p>-</p> <pre><code> tbl_fund --------- id (auto number) registration_id (FK to tbl_registration.id) </code></pre> <p>-</p> <pre><code> tbl_transaction --------------- id (auto number) fund_id (FK to tbl_fund.id) type shares price </code></pre> <p>I need to create a query that in psuedo-code would do the following:</p> <pre><code> SELECT port.*, SUM ALL transactions for each portfolio, FROM tbl_portfolio port INNER JOIN tbl_registration reg ON reg.portfolio_id = port.id LEFT JOIN tbl_fund fund on fund.registration_id = reg.id LEFT JOIN tbl_transaction trans ON trans.fund_id = fund.id </code></pre> <p>Now of course that query won't work...What I am needing essentially is to sum all the <strong>Price * Units</strong> for each fund, and then sum those together for each registration, and then sum all of that together for each portfolio.</p> <p>Each portfolio can have multiple registrations, and each registration can have multiple funds, and each fund can have multiple transactions.</p> <p>The last item that is throwing a stickler in this, there may be 10's or 100's of portfolios to count so I have no idea how to write the query, much less write it in an effective way that is not relying on subqueries that would cause it to have severely poor performance.</p> <p>Thank you for the help!</p> <p>Edit: PinnyM's answer works and queries the data correctly - however I should expand on the full need.</p> <p>Besides the tbl_transaction there is also a tbl_distri and tbl_div. Both have fund_id as FK to tbl_fund.id . I need to get the SUM's of tbl_distri.amount and tbl_div.units. So the full psuedo query would be something to the effect of:</p> <pre><code> SELECT port.*, SUM ALL transactions for each portfolio, SUM(div.units), SUM(distri.amount) FROM tbl_portfolio port INNER JOIN tbl_registration reg ON reg.portfolio_id = port.id LEFT JOIN tbl_fund fund on fund.registration_id = reg.id LEFT JOIN tbl_transaction trans ON trans.fund_id = fund.id LEFT JOIN tbl_distri distri on distri.fund_id = fund.id LEFT JOIN tbl_div div on div.fund_id = fund.id </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USw00tw00t111
    1. USw00tw00t111
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSum of All Related Rows with Matching ID MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO... I'm assuming `tbl_fund` has more columns in it, or what's the point? Also, why put the `tbl_...` prefix on everything - it's just so much noise.
      singulars
      1. POSum of All Related Rows with Matching ID MySQL
      1. USClockwork-Muse
    2. CODo you need to sum the number of shares, or the prices, or what? That is, what do you mean by "sum all the transactions"?
      singulars
      1. POSum of All Related Rows with Matching ID MySQL
      1. USMelanie
    3. COIf a portfolio can have multiple registrations, then your join from tbl_portfolio to tbl_registration will need to be a LEFT JOIN too.
      singulars
      1. POSum of All Related Rows with Matching ID MySQL
      1. USEvilBob22
 

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