mysql where count(column_name) = 1?

Question

Hi there,

Here is the query I am using:

SELECT k_id, COUNT(k_id) AS k_count 
FROM template_keyword_link 
WHERE k_id IN(1,2,3,4,5)
GROUP BY k_id;

This query returns something like
1 | 6
2 | 1
3 | 4
4 | 1
5 | 9

I want to add something like AND COUNT(k_id) = 1 so I end up with
2 | 1
4 | 1

However I get invalid use a group function.

How would I go about doing this?

---

Update:

Cheers guys,

The other part to my question is.

Can this be used as a delete statement?

something like

DELETE FROM 
template_keyword_link tkl
LEFT JOIN keywords k
ON tkl.k_id = k.k_id
WHERE tkl.k_id 
IN(SELECT k_id, COUNT(k_id) AS k_count 
FROM template_keyword_link 
WHERE k_id IN(1,2)
GROUP BY k_id
HAVING k_count = 1);

I get

You have an error in your SQL syntax;

---

So based on feedback i have altered this to use

DELETE tkl, k FROM 
template_keyword_link tkl
LEFT JOIN keywords k
ON tkl.k_id = k.k_id
WHERE tkl.k_id 
IN(SELECT k_id 
FROM template_keyword_link 
WHERE k_id IN(1,2)
GROUP BY k_id
HAVING COUNT(k_id) = 1);

However now I am getting

You can't specify target table 'tkl' for update in FROM clause

Answer 1

WHERE clause is applied before the COUNT(*) has been calculated, so you need in HAVING, that is applied after.

  SELECT k_id,
         COUNT(k_id) AS k_count 
    FROM template_keyword_link 
   WHERE k_id IN (1, 2, 3, 4, 5)
GROUP BY k_id
  HAVING k_count = 1

See also: http://dev.mysql.com/doc/refman/5.1/en/select.html

** UPD **:

TIAS ;-) btw, the query syntactically seems fine for me, BUT did not you forget to specify template_keyword_link and keywords join condition clause? Does mysql give you any errors?

Answer 2

You're looking for the having clause which happens after grouping (the where clause is before grouping):

SELECT k_id, COUNT(k_id) AS k_count 
FROM template_keyword_link 
WHERE k_id IN(1,2,3,4,5)
GROUP BY k_id
HAVING COUNT(k_id) = 1;

Answer 3

Nested subquery is a simple solution.

SELECT * FROM 
  (
    SELECT k_id, COUNT(k_id) AS k_count 
    FROM template_keyword_link 
    WHERE k_id IN(1,2,3,4,5)
    GROUP BY k_id 
  ) inner
WHERE inner.k_count = 1

Answer 4

try this

SELECT k_id, COUNT(k_id) AS k_count 
FROM template_keyword_link 
WHERE k_id IN(1,2,3,4,5)
GROUP BY k_id
HAVING ( COUNT(k_id) = 1 )