CS50 finance (Index) sqlite error near WHERE-CodePudding

This function is supposed to return a table that resumes all the transactions made grouping them by stock symbol. I'm stuck on this because I keep geting an error that seems to come from sqlite researche (transactions_sql) and more specially from the way I'm calling the user's id. Does someone can explain me what I'm doing wrong ? Did I not create the right link with the foreign key (id) on my transactions database ?

Here is the error message I get : RuntimeError: near "WHERE": syntax error

    @app.route("/")
@login_required
def index():
    """Show portfolio of stocks"""
    transactions_sql = db.execute("SELECT company_symbol, SUM(shares) FROM transactions GROUP BY company_symbol WHERE id IN (?)", session["user_id"])
    index = lookup(transactions_sql.company_symbol)
    value = index["price"] * int(transactions_sql.SUM(shares))
    cash_sql = db.execute("SELECT cash FROM users WHERE id IN (?)", session["user_id"])
    cash_left = float(cash_sql[0]["cash"])
    return render_template("index.html", transactions_sql=transactions_sql, index=index, value=value, cash_left=cash_left)

HTML {% extends "layout.html" %}

{% block title %}
Index
{% endblock %}

{% block main %}
<table >
    <thead>
        <tr>
            <th scope="col">Symbol</th>
            <th scope="col">Name</th>
            <th scope="col">Shares</th>
            <th scope="col">Current price</th>
            <th scope="col">Total value</th>
        </tr>
    </thead>
    <tbody>
        {% for transaction in transactions %}
        <tr>
            <th scope="row">{{ transactions_sql.company_symbol }}</th>
            <td>{{ index["name"] }}</td>
            <td>{{ transactions_sql.SUM(shares) }}</td>
            <td>{{ index["price"] | usd }}</td>
            <td>{{ index["value"]| usd }}</td>
        </tr>
        {% endfor %}
    </tbody>
    <tfoot>
        <tr>
            <td  colspan="4">Current cash balance</td>
            <td >{{ cash_left | usd }}</td>
        </tr>
        <tr>
            <td  colspan="4">TOTAL VALUE</td>
            <td >{{ cash_left | usd }}</td>
        </tr>
    </tfoot>
</table>
{% endblock %}

And here is my sqlite database :

sqlite> .schema
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT NOT NULL, hash TEXT NOT NULL, cash NUMERIC NOT NULL DEFAULT 10000.00);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX username ON users (username);
CREATE TABLE transactions(
    transaction_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    company_symbol TEXT NOT NULL,
    date DATETIME,
    shares NUMERIC NOT NULL,
    price NUMERIC NOT NULL,
    cost NUMERIC NOT NULL,
    id INTEGER,
    FOREIGN KEY (id)
        REFERENCES users (id));

Thanks in advance for your help !

CodePudding user response：

The problem is the syntax of this sql:

SELECT company_symbol, SUM(shares) FROM transactions GROUP BY company_symbol WHERE id IN (?)

A GROUP BY clause comes after the WHERE clause.

Here is the doc for reference.