SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Group by

  1. #1
    SitePoint Member
    Join Date
    Feb 2005
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by

    Hi All,
    I need a count of how many times certain field value appears in my table.
    Here is my code

    Select *, count(*) as counter
    Group by keyfield

    This gives me an error, thus how can I use group by on only one filed and still select rest of fields?
    Thanks
    Thanks

  2. #2
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With GROUP BY, you need a specific selection of columns and you need (in this case) to match those in the GROUP BY clause.

    Code:
    SELECT field1, field2, field3, field4, count(*) as counter
    FROM [Your Table Name Here]
    GROUP BY field1, field2, field3, field4
    -- optionally, this..
    HAVING count(*) > 5   -- or whatever
    
    -- optionally this..
    ORDER BY count(*) DESC

    edit:
    I must warn you though, if you're counting "States", for example, then just select and group-by Table.State, not everything in the table. -This defeats the purpose of the group-by.

    I need a count of how many times certain field value appears in my table
    The real answer to your question doesn't even require a GROUP BY.
    SELECT COUNT(*) AS [My Count] FROM TABLE_NAME WHERE THAT_FIELD = 'THAT CERTAIN VALUE'

    Dan
    Last edited by danfran; Feb 14, 2005 at 16:30.


Bookmarks

Posting Permissions

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