Geek asks and answers

SQL row counting

Before continuing, I do not want to use ANY query, which includes selecting all rows and counting the occurrences manually. By the way, I am doing this in PHP.

Basically, I have a table of prohibitions. Each new entry / line is a new prohibition. A box called user_namewhich player was blocked. Is there any way to calculate who has the most prohibitions in this table? I really don't want to select each row and then count it for each player. The table is quite large, therefore, to make this decision inappropriate and inefficient.

+3
source share
3 answers

This is done using aggregates COUNT(), grouping user_name.

:

SELECT
  user_name,
  COUNT(*) as numbans
FROM bans
GROUP BY user_name
/* ordered by number of bans from greatest to least */
ORDER BY numbans DESC

:

SELECT
  user_name,
  COUNT(*) as numbans
FROM bans
GROUP BY user_name
ORDER BY numbans DESC
/* adjust LIMIT for how many records you want returned -- 1 gives only the first record */
LIMIT 1

, PHP. , . RDBMS , . GROUP BY MySQL . .

: 3

SELECT
  user_name,
  COUNT(*) as numbans
FROM bans
GROUP BY user_name
/* HAVING clause limits results of an aggregate like COUNT()  (which you cannot do in the WHERE clause) */
HAVING numbans >= 3
+4

10 ( limit, ):

select user_name, count(user_name) as num_of_bans
from bans
group by user_name
order by num_of_bans desc
limit 10
+2

SELECT username, COUNT (id) FROM table_name GROUP BYuser_name

+1
source

More articles:


All Articles