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
SELECTeverything from the table in a query and
- also count for each row how often
aof the respectively row appears in
cin 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
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;
count is always
How can I achieve what I described above in one query? I'd appreciate any help! :)