2

I am trying to determine the total number of contributors in an image database who have contributed at least five images. The following query will give me counts of images by contributor, but that's only part of the puzzle. I'm sure this is simple to someone who has more knowledge than me :)

SELECT count(*) AS i_count,contributor_id FROM images GROUP BY contributor_id ORDER BY i_count DESC

5

To count how many contributors have contributed 5 images or more:

SELECT COUNT(*) AS number_of_contributors
FROM
  ( SELECT 1
    FROM images 
    GROUP BY contributor_id
    HAVING COUNT(*) >= 5
  ) AS t ;

It could be written without the derived table but it's obfuscated:

SELECT COUNT(*) OVER () AS number_of_contributors
FROM images 
GROUP BY contributor_id
HAVING COUNT(*) >= 5 
LIMIT 1 ;

(there is a slight difference between the two queries. If there are no contributors with 5+ images, the first query will return one row with 0. The second query will return no rows.)

  • So what does the 1 represent in SELECT 1 in the derived table? – andrewniesen Mar 25 '15 at 1:35
  • 1
    Also - please forgive the noob questions - what is the purpose of AS t? – andrewniesen Mar 25 '15 at 1:39
  • 3
    @andrewniesen: 1is just an integer value, the most basic, cheap thing to put there, because in the next query level only the number of rows matters. AS t is a table alias. Basic SQL questions are better directed to stackoverflow.com. – Erwin Brandstetter Mar 25 '15 at 3:08
  • What Erwin said above. I could be SELECT NULL or SELECT contributer_id instead of SELECT 1. We just need something there so we have a row for each contributor that then the external query can count them (the rows of t, the rows that the internal query produces). – ypercubeᵀᴹ Mar 25 '15 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.