count for each row

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'

Than开发者_JS百科ks

Does this work?

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

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

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');