Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: Quering a table on itself to find other counts

    Hey Gang,

    I have a blank spot with a query, so any suggestions are welcome, as it is doing my head in.

    I have a table which contains CustomerIDs and assoicated MemberIDs (which in fact are also rebranded CustomerIDs). In other words what customers belong to another customers group, and a customer cannot belong to their own group. So the typical structure is: -

    CustomerID, MemberID
    1, 2
    1, 3
    1, 4
    1, 5
    1, 9
    2, 4
    2, 5
    2, 9
    2, 10
    3, 34
    3, 42

    I would like to find out to things. Firstly, how many members per customer. This is easy enough, via: -

    SELECT CustomerID, COUNT(MemberID) AS Members
    FROM tblCustomerGroups;

    However, my problem is with my second query. I would like to find out how many members also belong to other people, per customer. So using the sample data above, my result would look like: -

    CustomerID, Members, Overlapping
    1, 5, 3
    2, 4, 3
    3, 2, 0

    As mentioned, getting the first two columns is easy enoough. The problem is getting the third column. This column represent members of a customer who are also members of another customer.

    So, looking at the sample data above: -

    Customer 1 has 5 members and 3 are overlapping, i.e. also belong to other customers, in this case Customer 2
    Customer 2 has 4 members and 3 are overlapping, i.e. also belong to other customers, in this case Customer 1
    Customer 3 has 2 members and 0 are overlapping, i.e. none belong to other customers.

    It sounds simple I know, but my brain is mangled due to other rather annoying and overcomplicated issues with this existong database.


  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    Well - my brain is tired too so just I'll throw out some suggestions for you to think about.

    1) Simple group by and count for the first two columns. A corrolated subselect that counts the number of rows in the table where the memberid matches customerid.
    2) Left outer join from your table to a derived table which is simply a select distinct of all the customerids in your table, joining the memberids to this distinct list of customerids. Group by and count(*) for your first two columns, count(TheDistinctCustomerIDs) for your third column.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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