Database Consultation

Navigation
1

I have a table of bank movements of many users that can be repeated throughout it. The table has different fields such as Box, UserName, IDN, Date, Amount ...

What I want to do is a ranking of boxes with more users sorted by the DNI (without being repeated). I mean I have: 

Alberto - caja1 - dni1 - fecha1 - importe1
David - caja2 - dni2 - fecha2 - importe2
Carlos - caja3 - dni3 - fecha3 - importe3
Miguel - caja1 - dni4 - fecha4 - importe4
Roberto - caja2 - dni5 - fecha5 - importe5
Luis - caja3 - dni6 - fecha6 - importe6
Nacho - caja1 - dni7 - fecha7 - importe7
Alberto - caja1 - dni1 - fecha8 - importe8

And what I want is to appear: 

Caja1 - 3
Caja2 - 2
Caja3 - 2

What I already have done is: 

SELECT Caja, COUNT(DISTINCT dni) as Usuarios
FROM miTabla
GROUP BY Caja

But this brings me all the boxes with their respective users and not the ones that have the most users.

Thank you very much!

    
asked by Miguel 31.01.2018 в 10:51
source

1 answer

1

According to what I am understanding, if what you want is to show you the number of different users who have used each box, the query would be the one you have.

But if what you want is that regardless of whether a user has used two or more times a box, show all the users that have used each one the query would be the following: 

SELECT Caja, COUNT(dni) as Usuarios
FROM calc
GROUP BY Caja

That is, without the distinct. You would also be worth the following query: 

SELECT Caja, COUNT(*) as Usuarios
FROM calc
GROUP BY Caja

The output of these queries is this:

  

caja1 - 4

     

caja2 - 2

     

caja3 - 2

On the other hand, if what you want is to only show you the box that most users have had the query would be the following: 

SELECT Caja, COUNT(*) as Usuarios
FROM calc
GROUP BY Caja
order by COUNT(*) desc
limit 1

The output of this query is this:

  

caja1 - 4

    
answered by 31.01.2018 / 11:38
source
Typescript: Executor Task Interface Error updating database