get the sum of an attribute in sql

Question

get the sum of an attribute in sql

Navigation

#1 by (2 votes)

2

I have these two tables

I want to get the sum of the states of all the users, for example

user1: estado=1
user1: estado=0
user2: estado=1
user1: estado=0
user1: estado=1
user1: estado=1
user2: estado=0
user2: estado=1

I want the sum, the result would be:

for user 1: state sum 1 = 3, state sum 0 = 2,

for user 2: state sum 1 = 2, state sum 0 = 2

I was trying:

objBD.query('SELECT * from USUARIO ', function(err, rows, fields) {
    //debo usa un for para la consulta de abajo
        objBD.query('SELECT SUM(estado) from USUARIO u INNER JOIN INTENTO i ON u.ID_U=i.ID_U where estado=? and u.ID_U=?',[1,rows[0].ID_U], function(err, rows, fields) {
            console.log(rows);          
        });
    });

My query is only meant for the value of estado=0 , I should do the same for estado=1 , I do not think it's the best way, I want you to help me create a cleaner query.

mysql sql

asked by hubman 08.01.2017 в 05:05

source

1 answer

How can you increase dates in a shell? Pass ResultSet to a JSON in Java JSP

score 2 · Accepted Answer

In this case, it's not the SUM function you should use, because it's not that you want to add the value of the estado field. In fact, this will give you incorrect results when estado = 0 , because the sum will always result in 0. Rather, you want to use the function COUNT() to count the records with the value estado desired:

select count(*)
  from usuario u
  join intento i
    on i.id_u = u.id_u
   and i.estado = ?
 where u.id_u = ?

Edit

If you want to obtain the accounts of each state for a particular user, you can do it using the function COUNT() with an expression CASE :

select count(case when i.estado = 0 then 'X' end) as cnt_estado_0,
       count(case when i.estado = 1 then 'X' end) as cnt_estado_1
  from usuario u
  join intento i
    on i.id_u = u.id_u
 where u.id_u = ?

And, if the desire is to obtain these accounts for all users of a single, you can modify the query in this way (note that here the LEFT JOIN is necessary if it is possible that a user does not have records in the table intento ):

select u.id_u,
       count(case when i.estado = 0 then 'X' end) as cnt_estado_0,
       count(case when i.estado = 1 then 'X' end) as cnt_estado_1
  from usuario u
  left join intento i
    on i.id_u = u.id_u
 group by u.id_u