Correct syntax for COUNT(DISTINCT...)

Question

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

added the error, I'm first getting the count to determine how many pages to spread the results to, this seems to be fairly normal see: http://stackoverflow.com/questions/818567/mysql-pagination-without-double-querying — dlofrodloh, May 10 '16 at 20:33
The top voted answer at your link indicates that isn't the way to go. It runs the query twice unnecessarily. Use the `LIMIT` clause to properly support pagination. — Michael McGriff, May 10 '16 at 20:36
That answer is nice and simple but I'd also need to have the number of pages and a figure of how many results there are, not just a next button — dlofrodloh, May 10 '16 at 20:41
@MichaelMcGriff -- I think it really depends on the situation. If the requirements need paging, then running multiple queries is generally the preferred method. If a "show more" approach can work, then that is definitely another option. As usual, it just depends. — sgeddes, May 10 '16 at 20:42

score 2 · Accepted Answer · answered May 10 '16 at 20:32

2

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.

answered May 10 '16 at 20:32

sgeddes

60,365
6
54
76

Do you know why I'm getting the error "Duplicate column name 'userID' " when turning this into a subquery? – dlofrodloh May 10 '16 at 20:45
@dlofrodloh -- Most likely both the candidate table and the note tables have a `userId` field. The subquery cannot determine which to use, hence the error. You would need to list out and alias each of the column names. You probably only need the columns that define a unique set though. – sgeddes May 10 '16 at 20:48
Aha, great thanks... I didn't need all the columns of course for the count, so I just replaced the asterixes with unique columns and it worked – dlofrodloh May 10 '16 at 21:10

Correct syntax for COUNT(DISTINCT...)

1 Answers1