SQL Count group by records

Posted on 2014-08-24
Last Modified: 2014-08-29

i am trying to create an sql statement to count particular group by rows

i am currently using the folloing sql statement

SELECT COUNT(*) AS count FROM photos WHERE status='active' GROUP BY photographer

which im tying to get yield 3 however it returns 797

what am i missing

Question by:M. Jayme Nagy
    LVL 58

    Expert Comment

    Why attach an Excel spreadsheet instead of table dump?
    LVL 6

    Accepted Solution


    Im not sure i thought it was an easy option

    however i seem to arrived at the results i was looking for

    SELECT COUNT(DISTINCT photographer) AS count FROM photos WHERE status='active'

    LVL 107

    Expert Comment

    by:Ray Paseur
    For future reference, GROUP BY can work together with ORDER BY.
    LVL 47

    Expert Comment

    it was a good lesson

    If you are trying to count the photographers by rummaging through the photos, then you need DISTINCT

    What you had with the first query is the number of photos WHERE status='active'

    Not absolutely sure what Ray was alluding to, but to get a count per photographer would require a group by

    SELECT photographer, COUNT(*) AS cnt FROM photos WHERE status='active' GROUP BY photographer
    LVL 6

    Author Closing Comment

    by:M. Jayme Nagy
    i answered my own question with trail and error

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This is a general how to create your own custom plugin system for your PHP application that you designed (or wish to extend a third party program to have plugin functionality that doesn't have it yet).  This is not how to make plugins for existing s…
    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now