dlofrodloh dlofrodloh - 6 months ago 17
SQL Question

Correct syntax for COUNT(DISTINCT...)

I've got the following SQL query and I'm trying to implement pagination, so I first want to get the COUNT of the result:

The normal query (works fine)

SELECT DISTINCT c.*, p1.*, username FROM candidate c
LEFT JOIN note p1 ON (c.candID = p1.candidateID)
LEFT JOIN user ON p1.userID = user.id
LEFT OUTER JOIN note p2 ON
(c.candID = p2.candidateID AND (p1.noteID < p2.noteID))
WHERE p2.candidateID IS NULL ORDER BY c.firstname ASC


I've tried the following, but it throws an error and I'm not sure what correct syntax to use:

Attempting to count the results (doesn't work)

SELECT COUNT(DISTINCT c.*, p1.*, username) FROM candidate c
LEFT JOIN note p1 ON (c.candID = p1.candidateID)
LEFT JOIN user ON p1.userID = user.id
LEFT OUTER JOIN note p2 ON
(c.candID = p2.candidateID AND (p1.noteID < p2.noteID))
WHERE p2.candidateID IS NULL ORDER BY c.firstname ASC


The error:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', p1., username) FROM candidate c LEFT ' at line 1

Answer

One option is to use a subquery:

SELECT COUNT(*)
FROM (
    SELECT DISTINCT c.*, p1.*, username FROM candidate c 
    LEFT JOIN note p1 ON (c.candID = p1.candidateID) 
    LEFT JOIN user ON p1.userID = user.id 
    LEFT OUTER JOIN note p2 ON 
    (c.candID = p2.candidateID AND (p1.noteID < p2.noteID)) 
    WHERE p2.candidateID IS NULL 
) t

Depending on your data, you may be able to do this without the subquery, but you cannot use multiple columns with the count aggregate -- that's what is causing your error.