Post reply

counting number of entries of a combo of columns

  • December 7, 2006 at 7:32 am

    #118238

    i want to select all from a table where a combo of a userID and a subscriptionid appear more than once. I can do this with one column, but not two. any ideas how i might do this

  • December 7, 2006 at 7:35 am

    #676600

    Use a derived table to locate the duplicates. Join to the derived table to return the rest of the columns for those records.

    Select t.*

    From YourTable As t

    Inner Join

    (

      Select UserID, SubscriptionID

      From YourTable

      Group By

      UserID, SubscriptionID

      Having Count(*) >= 2

    ) dt

      On (dt.UserID = t.UserID And

            dt.SubscriptionID = t.SubscriptionID)

  • December 7, 2006 at 4:02 pm

    #676764

    Select UserID, SubscriptionID from YourTable

    group by UserID, SubscriptionID

    having count(cast(UserID as varchar) + '_' + cast(SubscriptionID as varchar))>1

    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply