#1
  1. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785

    How to select a portion of a grouping


    Hello,

    I am not quite sure how to say this but I will give it a shot. I have a database table containing some duplication. The duplication is occuring on a field called sStrippedName.

    The issue I am having is that I need to know which of these rows have duplicate (2 or more) sStrippedName - but of these groupings I need to know which have 1 or more iExternalID NOT NULL or not '' - its possible that in 2 pairs one can be null or empty or both can be null or empty. So i cannot just also say where external id is not null because that eliminates too many.

    Here is what the query looks like without the selection of groupings containing at least one iExternalID not null:

    Code:
    mysql> select count(sStrippedName) as rcount, sStrippedName from Shop group by sStrippedName having rcount > 1;
    +--------+-----------------------------------------------+
    | rcount | sStrippedName                                 |
    +--------+-----------------------------------------------+
    |      2 | 1-day-paint-body-ctr-albuquerque              |
    |      2 | 1-day-paint-body-ctr-las-vegas                |
    |      3 | 911-collision-ctr-tucson                      |
    |      2 | a-auto-tech-miami                             |
    |      2 | a-js-body-shop-cleveland                      |
    |      2 | a-paint-body-shop-dallas                      |
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,260
    Rep Power
    4279
    Code:
    SELECT COUNT(sStrippedName) AS rcount
         , sStrippedName 
      FROM Shop 
    GROUP 
        BY sStrippedName 
    HAVING rcount > 1
       AND COUNT(CASE WHEN iExternalID > ''
                      THEN 'ok'
                      ELSE NULL END) > 0

    Comments on this post

    • Matt1776 agrees : Thanks! I shall commence to learn about 'CASE'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Recovering Intellectual
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2006
    Location
    Orange County, CA
    Posts
    1,306
    Rep Power
    785
    You sir, are exceptionally gifted with SQL. And I realize this wasn't breaking a sweat for you. Thanks again!
    Bugs that go away by themselves come back by themselves
    Beware - your loyalty will not be rewarded

IMN logo majestic logo threadwatch logo seochat tools logo