CODEWITHSUNDEEP
mysqlsql
ccdavies
ccdavies

Reputation: 1606

Ignore duplicate rows in COUNT

The following query gives a count of how many rows there are in the exp_judging table with a value of 1 in the field pre, where there is also a matching column in exp_submissions with a member_group of 5

SELECT COUNT(*) AS count_result
  FROM exp_judging AS jud
  LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
  WHERE jud.pre = 1
        AND sub.member_group = 5

This works great. However, in the future there will be multiple rows with the same 'rel_id' in the 'exp_judging' table.

How can I ensure that only one of each row is counted?

I tried adding GROUP BY to the end, but it just shows 1 result then, instead of 4 before. (there are no duplicates yet)

GROUP BY jud.rel_id

EDIT:

Have tried the following, but still just shows 1:

SELECT COUNT(DISTINCT jud.rel_id) AS count_result
FROM exp_judging AS jud
LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
WHERE jud.pre = 1
AND sub.member_group = $member_group
GROUP BY jud.rel_id

Upvotes: 8

Views: 11146

Answers (2)

Nitu Bansal
Nitu Bansal

Reputation: 3856

try below query

 SELECT COUNT(jud.rel_id) AS count_result
 FROM exp_judging AS jud
 LEFT JOIN exp_submissions AS sub ON jud.rel_id = sub.id 
 WHERE jud.pre = 1
    AND sub.member_group = 5
group by jud.rel_id

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Use COUNT(DISTINCT rel_id) instead of COUNT(*).

Upvotes: 25

Related Questions