Complex SQL query

Question

I have the these tables:

- Users
    - id
- Photos
    - id
    - user_id
- Classifications
    - id
    - user_id
    - photo_id

I would like to order Users by the total number of Photos + Classifications which they own.

I wrote this query:

SELECT users.id, 
COUNT(photos.id) AS n_photo, 
COUNT(classifications.id) AS n_classifications, 
(COUNT(photos.id) + COUNT(classifications.id)) AS n_sum 
FROM users 
LEFT JOIN photos ON (photos.user_id = users.id) 
LEFT JOIN classifications ON (classifications.user_id = users.id) 
GROUP BY users.id 
ORDER BY (COUNT(photos.id) + COUNT(classifications.id)) DESC

The problem is that this query does not work as I expect and returns high numbers while I have only a few photos and classifications in the db. It returns something like this:

id n_photo n_classifications n_sum
29  19241 19241                 38482
16  16905 16905                 33810
1    431  0                      431
...

Answer 1

You are missing distinct.

  SELECT U.ID, COUNT(DISTINCT P.Id)+COUNT(DISTINCT C.Id) Count
  FROM User U
  LEFT JOIN Photos P ON P.User_Id=U.Id
  LEFT JOIN Classifications C ON C.User_Id=U.Id
  GROUP BY U.Id
  ORDER BY COUNT(DISTINCT P.Id)+COUNT(DISTINCT C.ID)

Answer 2

I could be misinterpreting your schema, but shouldn't this:

LEFT JOIN classifications ON (classifications.user_id = users.id)

Be this:

LEFT JOIN classifications ON (classifications.user_id = users.id) 
                         AND (classifications.photo_id = photos.id)

?

Answer 3

SELECT users1.id, users1.n_photo, users2.n_classifications
FROM (
    SELECT users.id, COUNT(photos.id) AS n_photo
    FROM users LEFT OUTER JOIN photos ON photos.user_id = users.id
    GROUP BY users.id
  ) users1
  INNER JOIN (
    SELECT users.id, COUNT(classifications.id) AS n_classifications
    FROM users LEFT OUTER JOIN classifications ON classifications.user_id = users.id
    GROUP BY users.id
  ) users2 ON users1.id = users1.id

Answer 4

Try something more like this instead:

SELECT users.id as n_id,
(SELECT COUNT(photos.id) FROM photos WHERE photos.user_id = n_id) AS n_photos,
(SELECT COUNT(classifications,id) FROM classifications WHERE classifications.user_id = n_id) AS n_classifications,
(n_photos + n_classifications) AS n_sum
FROM users
GROUP BY n_id
ORDER BY n_sum DESC