CODEWITHSUNDEEP
sqlsqliteandroid-sqlite
Bob
Bob

Reputation: 23010

How can I have Sub Sum column in sql query?

I have the following table in Sqlite

Person1     Person2     Amount
A           X           1000
A           Y           2000
A           Z           5000
B           X           3000
B           Y           4000

how can I write a query to add an extra column that its value is the sum of Amount for each Person in Column Person1.

I need the following result:

Person1     Person2     Amount      SumAmountPerson1
A           X           1000        8000
A           Y           2000        8000
A           Z           5000        8000
B           X           3000        5000
B           Y           2000        5000

my query is:

select *, Sum(Amount) from MyTable 

GROUP by Person1

But it returns the following result set in Sqlite:

Person1     Person2     Amount      SumAmountPerson1
A           X           1000        8000
B           X           3000        5000

But I need all rows

Upvotes: 1

Views: 450

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

SQLite does not support window functions, so one option to get your result would be to use a subquery to aggregate amounts by the first person, and then join this back to your original table.

SELECT t1.Person1,
       t1.Person2,
       t1.Amount,
       t2.SumAmountPerson1
FROM yourTable t1
INNER JOIN
(
    SELECT Person1, SUM(Amount) AS SumAmountPerson1
    FROM yourTable
    GROUP BY Person1
) t2
    ON t1.Person1 = t2.Person1

Upvotes: 3

Related Questions