current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

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

share|improve this question
up vote 2 down vote accepted

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.)

share|improve this answer
    
So what does the 1 represent in SELECT 1 in the derived table? – andrewniesen Mar 25 '15 at 1:35
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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