0

So in index i'm trying to display a table with all the stocks that the user has bought, one row for each stock, displaying the total number of shares in the user's portfolio along with the total value of these shares, the issue is that by using GROUP BY symbol the table only shows the latest purchase of the stock and not all the purchases the user has made.

for example, assuming that the user has bought 7 NFLX shares sometime in the past and decided to buy 4 more, by using GROUP BY symbol, it doesn't show that the user has 11 NFLX shares, it shows that they have only 4, cuz that's the latest purchase.

enter image description here

i reckon that the reason for this is the way i store users' data, which is in one big table called transactions that includes all the users activity that can later be sorted by their ID

the code for inserting data into the transactions table in the buy route:
db.execute("INSERT INTO transactions (user_id, symbol, name, shares, price, total, time) VALUES (:user_id, :symbol, :name, :shares, :price, :total, :time)", user_id=session['user_id'], symbol=quote['symbol'], name=quote['name'], shares=shares, price=quote['price'], total=stock, time=datetime.datetime.now())

In trying to overcome this, i tried to GROUP BY symbol, shares it yielded better results in showing how many shares the user has of each stock thus accurately calculating their values. The only problem is that it shows each purchase of the stock instead of grouping them all together.

Meaning, that in the aforementioned example, by using GROUP BY symbol, shares what's displayed is two rows for NFLX, one with the 7 shares and the other with the 4. this could be useful for when i come around to working on history. but not on index.

enter image description here

This is the code i used for index:

@app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    portfolio = db.execute("SELECT symbol, name, shares, price, total FROM transactions WHERE user_id = :user_id GROUP BY symbol", user_id=session['user_id'])
    monz = db.execute("SELECT cash FROM users WHERE id = :id",
                          id=session['user_id'])
    cash = monz[0]['cash']
    total = cash

    for subtotal in range(len(portfolio)):
        total += portfolio[subtotal]['total']

    return render_template("index.html", portfolio=portfolio, cash=cash, total=total)

and this is the html:

{% extends "layout.html" %}

{% block title %}
    Finance.
{% endblock %}

{% block main %}
<table class="table table-striped">
  <thead>
    <tr>
      <th scope="col">Symbol</th>
      <th scope="col">Name</th>
      <th scope="col">Shares</th>
      <th scope="col">Price</th>
      <th scope="col">Total</th>
    </tr>
  </thead>
  <tbody>
    {% for row in portfolio %}
      {% if loop.index0 < loop.length%}
        <tr>
          <td>{{ portfolio[loop.index0]['symbol'] }}</td>
          <td>{{ portfolio[loop.index0]['name'] }}</td>
          <td>{{ portfolio[loop.index0]['shares'] }}</td>
          <td>{{ portfolio[loop.index0]['price'] | usd}}</td>
          <td>{{ portfolio[loop.index0]['total'] | usd }}</td>
        </tr>
      {% endif %}
    {% endfor %}

    <tr>
      <th>CASH</th>
      <td></td>
      <td></td>
      <td></td>
      <td>{{ cash | usd }}</td>
    </tr>

    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <th>{{ total | usd }}</th>
    </tr>
  </tbody>
</table>
{% endblock %}

I admit i don't have the firmest grasp on GROUP BY yet, so help and tips are greatly appreciated. Thanks.

1

BY doing "GROUP BY symbol, shares", it will do exactly that - any rows of AAPL, 4 shares will be grouped together, and any AAPL, 3 shares will be in a separate grouping.

try "GROUP BY userid, symbol" instead.

You also need to throw in a "SUM(shares)" clause. Maybe this link will help you:

https://www.tutorialspoint.com/sql/sql-group-by.htm

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .