CODEWITHSUNDEEP
mysqlsql
Zixvy
Zixvy

Reputation: 7

How to not sum all row in table

This is my table : 

+---------+-----+
| id_user | pax |
+---------+-----+
|       2 |   2 |
|       2 |   2 | 
|       3 |   2 |
|       3 |   2 |
+---------+-----+

So i have a 4 row of data

How do i sum just 1 of each row id_user ? so the result of sum i want to get is 4 not 8

Upvotes: 0

Views: 58

Answers (3)

P.Salmon
P.Salmon

Reputation: 17615

Not clear what you are after but here's some possibilities.

drop table if exists t;
create table t
(id_user int,pax int);
insert into t values
(       2 ,   2 ),
(       2 ,   2 ), 
#(       2 ,   3 ), 
(       3 ,   2 ),
(       3 ,   2 );


select sum(pax) sumpax
from 
(select distinct id_user,pax from t) s;

+--------+
| sumpax |
+--------+
|      4 |
+--------+
1 row in set (0.00 sec)

select id_user,sum(pax) sumpax
from
(select distinct id_user,pax from t) s
group by id_user with rollup;

+---------+--------+
| id_user | sumpax |
+---------+--------+
|       2 |      2 |
|       3 |      2 |
|    NULL |      4 |
+---------+--------+
3 rows in set (0.00 sec)

select id_user,sum(pax) sumpax
from t
group by id_user with rollup;

+---------+--------+
| id_user | sumpax |
+---------+--------+
|       2 |      4 |
|       3 |      4 |
|    NULL |      8 |
+---------+--------+
3 rows in set (0.00 sec)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would recommend using row_number():

select sum(pax)
from (select t.*,
             row_number() over (partition by id_user order by pax) as seqnum
      from t
     ) t
where seqnum = 1;

row_number() gives you flexibility on which value to use (in this case it chooses the minimum pax value). It also allows you to include other information, such as the total count of rows.

Note: Have a column such as pax repeated on different rows suggests that your data is not normalized. Sometimes this happens when working with summary tables. However, more often, it suggests a problem with the data model that should be fixed by having a table with one row per id_user and pax value.

Upvotes: 0

artemis
artemis

Reputation: 7241

You need GROUP BY.

SELECT
    id_user,
    SUM(pax)

FROM
    myTable

GROUP BY
    id_user

See this SQL FIDDLE for example using your data.

Here is some documentation.

Upvotes: 2

Related Questions