mysql - how to display count of a value in an extra column

Question

Imagine we have a table like this:

id  value
 1      a
 2      b
 3      a
 4      a
 5      b

Query like this

SELECT * , COUNT( * )
FROM test
GROUP BY value

gives us a table like this:

id  value COUNT(*)
 1      a       3
 2      b       2

which tells us that there are three 'a' and two 'b' in our table.

The question is: is it possible to make a query (without nested SELECT's), which would yield a table like

id  value count_in_col
 1      a            3
 2      b            2
 3      a            3
 4      a            3
 5      b            2

The goal is to avoid collapsing columns and to add quantity of 'value' elements in the whole column to each row.

spencer7593 · Accepted Answer · 2013-11-26 01:49:05Z

2

Yes, it's possible to return the specified resultset using only a single SELECT keyword.

 SELECT t.id
      , t.value
      , COUNT(DISTINCT u.id) AS count_in_col
   FROM mytable t
   JOIN mytable u
     ON u.value = t.value
  GROUP
     BY t.id

to setup test case:

 CREATE TABLE `mytable` (`id` INT, `value` VARCHAR(1));
 INSERT INTO `mytable` VALUES (1,'a'), (2,'b'),(3,'a'),(4,'a'),(5,'b');

returns:

    id  value   count_in_col  
------  ------  --------------
     1  a                    3
     2  b                    2
     3  a                    3
     4  a                    3
     5  b                    2

NOTE:

This assumes that id is unique in the table, as would be enforced by a primary key or unique key constraint.

In terms of performance, depending on cardinality, an index ... ON (value,id) may improve performance.

This approach (using a JOIN to match rows on the value column) does have the potential to produce a very large intermediate resultset, if there are a "lot" of rows that match on value. For example, if there are 1,000 rows with value='a', the intermediate resultset for those rows will be 1,000*1,000 = 1,000,000 rows.

Adding a predicate (in the ON clause) may also improve performance, but reducing the number of rows in the intermediate result.

    ON u.value = t.value
   AND u.id >= t.id

(There's no real magic; the "trick" is to use COUNT(DISTINCT id) to avoid the same id value from being counted more than once.)

edited Nov 26, 2013 at 1:49

answered Nov 24, 2013 at 3:42

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3026384 Over a year ago

I really don't quite understand the magic, but nevertheless thanks a lot!

spencer7593 Over a year ago

@user3026384: it's not clear why you want to avoid a second SELECT within the query. In terms of performance, the approach of using a JOIN operation to match rows on the value column has the potential to produce a very large number of rows, when there are a large number of rows with matching value. (Each row with value='a' is going to get matched with every row with value='a', for example, if there are 1000 such rows, that will produce an intermediate resultset of 1,000,000 rows. The "magic trick" is to use COUNT(DISTINCT to avoid counting duplicate ids from those million rows.

Filipe Silva · Accepted Answer · 2013-11-24 03:24:30Z

1

I don't think that is possible without a subquery to count the number of occurrences of the different values:

SELECT a.*,b.valCount
FROM test a
INNER JOIN 
(
  SELECT value,COUNT(*) AS valCount
  FROM test
  GROUP BY value
) b
ON b.value = a.value
ORDER BY a.id;

sqlfiddle demo

answered Nov 24, 2013 at 3:24

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Comments

ApplePie · Accepted Answer · 2013-11-24 03:24:22Z

0

You need to use a subquery to group the values and do the count. Then you just have to join on that subquery on the value field.

SELECT test.id, test.value, t2.val_count 
FROM test INNER JOIN 
  (SELECT value, COUNT(value) AS val_count FROM test GROUP BY value) AS t2
  ON test.value = t2.value;

http://sqlfiddle.com/#!2/c8578/7

Edit: I just saw that you asked for an answer without using nested queries. I do not think this is possible but I will revise my answer if I find a way to do it.

answered Nov 24, 2013 at 3:24

ApplePie

8,9525 gold badges41 silver badges62 bronze badges

Comments

Jason Heo · Accepted Answer · 2013-11-24 03:35:42Z

0

Here is simple (and faster) SQL, but to print exact what you want, client program should parse output of GROUP_CONCAT

SELECT GROUP_CONCAT(id), value, COUNT( * )
FROM test
GROUP BY value;

answered Nov 24, 2013 at 3:35

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges

1 Comment

spencer7593 Over a year ago

Be aware that there is a limit on the size of the string returned by the GROUP_CONCAT function.

Collectives™ on Stack Overflow

mysql - how to display count of a value in an extra column

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related