mysql where count(column_name) = 1?

Question

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:

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

Could you add the exact query that gives you the error? – JoshD Sep 28 '10 at 01:06 — JoshD, Sep 28 '10 at 01:06
what u want is show the id , whose count is 1 – Pramendra Gupta Sep 28 '10 at 01:12 — Pramendra Gupta, Sep 28 '10 at 01:12

zerkms · Accepted Answer · 2010-09-28T01:17:43.650

11

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?

edited Sep 28 '10 at 01:17

answered Sep 28 '10 at 01:07

zerkms

249,484
69
436
539

Edited the query to include them ;) But as you can see, Im still getting an error. – Hailwood Sep 28 '10 at 01:22
because `IN` expects to get only one column, while you're giving 2. Move `count(*)` to `having`. – zerkms Sep 28 '10 at 01:27
yes, you cannot select from the table you're modifying now :-S – zerkms Sep 28 '10 at 01:36

score 3 · Answer 2 · answered Sep 28 '10 at 01:08

3

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;

answered Sep 28 '10 at 01:08

paxdiablo

854,327
234
1,573
1,953

score 0 · Answer 3 · answered Sep 28 '10 at 01:10

0

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

answered Sep 28 '10 at 01:10

burkestar

753
1
4
12

HAVING is simpler and more explicit. – Porculus Sep 28 '10 at 01:25

score 0 · Answer 4 · answered Sep 28 '10 at 01:14

0

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 )

answered Sep 28 '10 at 01:14

Pramendra Gupta

14,667
4
33
34

mysql where count(column_name) = 1?

4 Answers4