Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Unanswered: SQL Statement Help Please

    have a table of statistical information which can contain zero or entries for a given day. I would like a single statement to find which day has the most entries on it.

    The following statement will list the days and the number of entries for that day.

    SELECT Date, MAX(COUNT(ID)) FROM Stats WHERE CompanyD=2 GROUP BY Date

    Is there a way I can modify this statement so it returns a single row with the Date and the number of rows for that Date, but only for the Date with the most rows.

    Hope thats clear.

    Z

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select `Date`
         , count(ID) as rows
      from Stats 
     where CompanyD = 2 
    group 
        by `Date`
    order
        by rows desc
     limit 1
    tip: don't use a reserved word like Date for a column name, you are only asking for syntax errors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    2
    Many thanks.

    It is actually quite simple really isn't it.

    At least it is when you know how. Again thanks

    Z

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •