CODEWITHSUNDEEP
androidsqliteandroid-sqlite
user4404809
user4404809

Reputation:

How to properly sum and group by, a set of records with where clause in SQLite

I have a table of clients like the following:

ID  PNO PID EVENT       GID A     PS
1   04  1   P&R_A       2   1.0   2
2   03  1   P&R(j)      2   1.0   2
3   04  1   P&R(j)      2   1.0   2
4   04  1   P&R(j)      2   1.0   2
5   04  1   P&R(j)      2   1.0   3
6   03  1   P&R_A       2   1.0   2
7   02  1   LP          2   1.0   2
8   06  1   LP          2   0.5   1

When I run the following query 

SELECT PNO,EVENT, SUM(A) AS Atts, SUM(PS) AS Points FROM clients
WHERE ID = 1 GROUP BY PNO

I get only 

    PNO PID EVENT       GID Atts  Points
    02  1   LP          2   1.0   2
    03  1   P&R_A       2   2.0   4
    04  1   P&R(j)      2   4.0   9
    06  1   P&R(j)      2   0.5   1

and not

    PNO PID EVENT       GID Atts  Points
    02  1   LP          2   1.0   2
    03  1   P&R_A       2   2.0   4
    04  1   P&R(j)      2   3.0   6
    04  1   P&R_A       2   1.0   3
    06  1   P&R(j)      2   0.5   1

Upvotes: 1

Views: 76

Answers (2)

Sevle
Sevle

Reputation: 3119

You have to add both columns in your GROUP BY, something like:

SELECT PNO,EVENT, SUM(A) AS Atts, SUM(PS) AS Points FROM clients
WHERE ID = 1 GROUP BY PNO, EVENT

With GROUP BY you group the entries based on the uniqueness of both PNO and EVENT and then aggregate functions (SUM in your case, but also, AVG,MIN,MAX etc.) are calculated based on that 'grouping'.

Upvotes: 1

lescijus
lescijus

Reputation: 76

Add Event in group by clause.

SELECT PNO,EVENT, SUM(A) AS Atts, SUM(PS) AS Points FROM clients WHERE ID = 1 GROUP BY PNO, EVENT

Upvotes: 1

Related Questions