Home Forums SQL Server 2008 T-SQL (SS2K8) Count for each account no -parent and childsql RE: Count for each account no -parent and childsql

  • November 20, 2013 at 9:03 pm

    #1668009

    nutty (11/20/2013)

    Hi Guys

    I have a data where there are parent and child product.

    Each parent has got a unique code(P1) and a link code (L1) on their account

    and each child of the parent has got a seperate code (C12) but they share the same account no.

    I want to count the parent and children

    Here is a sample dataset

    Line_no code Account No

    123 C12 Ac111

    1222 C12 Ac111

    1243 C12 Ac111

    433 P1 Ac111

    433 L1 Ac111

    543 C1 Ac222

    544 C1 Ac222

    4322 P1 Ac222

    4322 L1 Ac222

    Now if I do a group by, I di get unique parents but not unique children

    Please help!!

    nutty (11/20/2013)

    Hi

    Apologies for not following the format. I will keep this in mind next time.

    My desired output is

    Parent_Count | Child_count

    2 7

    Thanks

    Mita

    nutty (11/20/2013)

    Hi Sean

    The child account does not have a link code. It only has the child code.

    so if I use this query for the whole dataset by just filtering on child code, it gives me the count of all the child codes instead of count of child codes belonging ot a particular account.

    I need to write a query which somehow ties the child accounts to their parent account using the common account no.

    Does that make sense?

    Yes but if you follow the progression of your posts, then "no". If you look at your first post and count all of the rows, you end up with 9 rows. 2 Parents and 7 "other" rows which include both Links (Lx rows) and Children (Cx rows). That happens to match exactly what you posted in the second quote above and Sean's code produces that output.

    So my question is, according to the data in your original post, what should the output actually look like because, right now, I'm totally confused by your posts. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)