Clever Geek Handbook

MySQL: use column value in same row for count

How do I use a value from another column so that I can create another column with a sum of the same type.

eg.

+---------+------+
|  name   | team |
+---------+------+
| Michael | red  |
| Lebron  | blue |
| Ben     | red  |
| Tiger   | red  |
| John    | blue |
+---------+------+

Output:

+---------+------+----------------+
|  name   | team | member_counter |
+---------+------+----------------+
| Michael | red  |              3 |
| Lebron  | blue |              2 |
| Ben     | red  |              3 |
| Tiger   | red  |              3 |
| John    | blue |              2 |
+---------+------+----------------+

As you can see there are 3 reds and 2 blues. Anyway, can I use the value of the previous columns? count (command = previous column)? how is member_counter?

+3


source to share


4 answers


You have to do join

with an additional selection of all commands

select name, team, teams.count 
 from names
 join (select team, count(1) as count from names group by team) as teams
using (team);


sqlfiddle

+1


source


You can JOIN

get a derived table containing team

counts:

SELECT m.name, m.team, t.cnt AS  member_counter
FROM mytable AS m
INNER JOIN (
   SELECT team, COUNT(*) AS cnt
   FROM mytable
   GROUP BY team ) t
ON m.team = t.team


It will probably be better than a correlated subquery.

Demo here

+1


source


  • If you only want an output like this, you can do:

    SELECT T1.name,T1.team,T2.member_count
FROM TableName T1 JOIN
  (SELECT name,team,count(team) as member_count
   FROM TableName
   GROUP BY team) T2 ON T1.team=T2.team

    Result:

    name    team    member_count
----------------------------
Michael red     3
Lebron  blue    2
Ben     red     3
Tiger   red     3
John    blue    2

    Example in SQL Fiddle .

  • If you are trying to add another column member_counter

    to your table, you can do this:

    ALTER TABLE TableName
ADD member_counter INT NULL


UPDATE TableName a 
   JOIN 
   (SELECT name,team,COUNT(team) as member_counter
    FROM TableName b
    GROUP BY team) b ON a.name = b.name  AND a.team=b.team
SET a.member_counter = b.member_counter

    Example in SQL Fiddle .

+1


source


Just do the subquery:

select *, (select count(*) from table t2 where t1.team = t2.team) as member_counter 
from table t1

EDIT:

I have checked 1 million lines. The execution plans for this request and the request from the received response are 99% identical. With time statistics, both queries do exactly the same thing. With IO statistics, both queries are executed exactly the same. And now I just can't figure out why I got down. The only difference in my version is shorter and clearer.

EDIT2:

This is a working fiddle:

select *, (select count(*) from tmp t2 where t1.team = t2.team) as member_counter 
from tmp t1
-1


source






More articles:


All Articles