count for each row

Question

Hi,

What is wrong with this query?

SELECT *, (SELECT COUNT(*)
FROM
(
    SELECT NULL
    FROM words
    WHERE project=projects.id
    GROUP BY word
    HAVING COUNT(*) > 1
) T1) FROM projects

MySQL returns 1054 Unknown column 'projects.id' in 'where clause'

Thanks

Answer 1

Your inner subquery knows nothing about the outer query, so the projects table is not available.

Answer 2

Does this work?

SELECT *, (SELECT COUNT(*)
    FROM words
    WHERE words.project=projects.id) as pCount
 FROM projects

Answer 3

It looks like you are trying to count for each project the number of words which occur more than once.

You can run your subquery for all projects and then use a JOIN to get the rest of the data from the projects table:

SELECT projects.*, COUNT(word) AS cnt
FROM projects
LEFT JOIN (
        SELECT project, word
        FROM words
        GROUP BY project, word
        HAVING COUNT(*) > 1
) T1
ON T1.project = projects.id
GROUP BY projects.id

Result:

id  cnt
1   0
2   1
3   2

Test data:

CREATE TABLE projects (id INT NOT NULL);
INSERT INTO projects (id) VALUES (1), (2), (3);

CREATE TABLE words (project INT NOT NULL, word VARCHAR(100) NOT NULL);
INSERT INTO words (project, word) VALUES
(1, 'a'),
(2, 'a'),
(2, 'b'),
(2, 'b'),
(3, 'b'),
(3, 'b'),
(3, 'c'),
(3, 'c');