Let's say I have the following table with these rows already inserted:

|  a  |   b   |      c      |
|---  |----   |-------      |
| 1A  |  1B   | (null)      |
| 2A  |  2B   |     1A      |
| 3A  |  3B   |     1A      |

I would now like to

  • SELECT everything from the table in a query and
  • also count for each row how often a of the respectively row appears in c in the whole table.

This is what the expected output should look like:

|  a  |   b   |      c      | count     |
|---  |----   |-------      |------     |
| 1A  |  1B   | (null)      |     2     |
| 2A  |  2B   |     1A      |     0     |
| 3A  |  3B   |     1A      |     0     |

For the first row, "1A" also appears in c of the second and third row. However, "2A" or "3A" doesn't appear anywhere else in c.

I've tried the following (SQL Fiddle):

create table test (a varchar(30), b varchar(30), c varchar(30));

insert into test (a, b) values ("1A", "1B");
insert into test (a, b, c) values ("2A", "2B", "1A");
insert into test (a, b, c) values ("3A", "3B", "1A");

select *, (select count(*) from test where test.c = a) as count from test;

However, count is always 0.

How can I achieve what I described above in one query? I'd appreciate any help! :)

  • 1
    SQL Server <> MySQL. What RDBMS are you really using? The fiddle doesn't load either, but you should be including your attempts in the question, not as an offsite resource. – Larnu Nov 7 at 12:02
  • Tagged with mysql based on the SQLFiddle – a_horse_with_no_name Nov 7 at 12:09

You can use a subquery:

select t.*,
       (select count(*) from test t2 where t2.c = t.a)
from test t;

This is just like your version but it uses correct table aliases. Always qualify all column references.


SELECT 0 AS Id, 'All' AS Name UNION ALL SELECT Column(s) FROM tableName;

You Can add like this in sql Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.