Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Unanswered: help with grouping columns

    Hi

    I have a query that i'm using to retrieve the counts of certain data, the problem is that I want to display two of the values and the others I would like to group them into an other group and have there counts combined. This is the statement I have so far.

    select colA, colB, count(colA)
    from tableA
    group by colA,colB;

    The statements gives all the values of colA but I would only like to see 2 values and the rest I would like to see them combined, with the name other assigned to the combined values.

    Any help would be much appreciated.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT colA2
         , colB
         , COUNT(*) AS total
      FROM ( SELECT CASE WHEN colA IN ( 'one','two' )
                         THEN colA
                         ELSE 'other' END   AS colA2
                    , colB
               FROM tableA ) AS dt
    GROUP
        BY colA2
         , colB
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    This worked perfectly

    Thanks for all your help

    Hally

Posting Permissions

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