jeudi 24 mars 2016

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

Aucun commentaire:

Enregistrer un commentaire