CODEWITHSUNDEEP
mysqlsql
user3026384
user3026384

Reputation: 15

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

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.

Upvotes: 1

Views: 2814

Answers (4)

spencer7593
spencer7593

Reputation: 108400

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.)

Upvotes: 2

Jason Heo
Jason Heo

Reputation: 10246

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;

Upvotes: 0

Filipe Silva
Filipe Silva

Reputation: 21657

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

Upvotes: 1

ApplePie
ApplePie

Reputation: 8942

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.

Upvotes: 0

Related Questions