help with select statement

  • looking for some assistance on selecting all accounts numbers from a table and counting and grouping them by account # prefix which is a two digit number. so a bunch of accounts start with 22, another bunch starts with 33, another with 44 and so on, i want to talley up how many accounts under each prefix.

  • how about

    select substring(account_number,1,2) as Acc_prefix, count(*) as Counter

    from your_accounting_table

    group by substring(account_number,1,2)

    order by Acc_prefix

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • appreciate the quick reply, in the learning phase of writing sql, so dont laugh, but what do you mean by substring? list select *? and than i guess i am trying to eliminate having to account for each prefix since there are so many

  • substring gets just the characters indicated by the parameters

    so

    substring('223456', 1, 2) will return '22'

    If you want the left side, also look at

    Left(string,length)

    For the above example it would be:

    Left('223456', 2) which would also return '22'

    Take a look-see here:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp

  • appreciate that, i wasnt thinking, now i understand the substring is a function, not something i enter, thanks, great help

  • Books Online is a valuable resource. It contains the help for SQL and SQLServer

    From Query Analyser, you can access it pressing Shift + F1.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • FYI - from my Query Analyzer, both shift-F1 and F1 bring up BOL.


    Terry

  • indeed F1 just brings up BOL,

    shift-F1 brings up BOL and positiones to the selected statement in possible.

    e.g. create index x on mytable (y)

    when you select "create index" and then push shift-F1 BOL will show up and be positioned to "create index"

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • quick question about the substring, i am running the following:

     

    --This will query and show all the account prefixes and total number of accounts in each prefix

    select substring(ad.account_number,1,2)as AcctPreFix, count(*) as Total

    from annual.annual_account_detail aad,

    common.common_account_detail cad,

    annual.annual_status aas

    where aad.ar_id = cad.account_id and cad.account_id

     

    How do i get it so that it only shows distinct account numbers, right now it shows me duplicates, and i have tried the distinct but it keeps complaining, not sure where i should throw in the distinct

  • try something like this:

    select substring(ad.account_number,1,2)as AcctPreFix, count(*) as Total

    from annual.annual_account_detail aad,

    common.common_account_detail cad,

    annual.annual_status aas

    where aad.ar_id = cad.account_id and cad.account_id

    Group BY ad.account_number

     


    Terry

  • getting closer, how come it shows me the prefix twice in some cases:

    --This will query and show all the account prefixes and total number of accounts in each prefix

    select distinct substring(aad.account_number,1,2)as AcctPreFix, count(*) as Total

    from annual.annual_account_detail aad,

    common.common_account_detail cad,

    annual.annual_status aas

    where aad.ar_id = cad.account_id and cad.account_id not in

    (select cad.account_id where cad.combined_account_code in ('C','R') and

    cad.sub_account in ('00','AA') ) and

    cad.portfolio_manager not in (

                                          'HD1',

                                          'DA1',

                                          'JV1',

                                          'LC1',

                                          'MT1',

                                          'RC1',

                                          'MD1',

                                          'LG1',

                                          'NA1',

                                          'AI1',

                                          'RG1',

                                          'LL1',

                                          'CP1',

                                          'NQ1',

                                          'DM2',

                                          'MV2',

                                          'SD2',

                                          'MG2',

                                          'NA2',

                                          'KI2',

                                          'GS2',

                                          'RS1',

                                          'SG3',

                                          'GL3',

                                          'IC3',

                                          'NQ4',

                                          'MX5',

                                          'MD5',

                                          'MB5',

                                          'MA5',

                                          'IC5',

                                          'GT5',

                                          'MM5',

                                          'NU5',

                                          'CS6',

                                          'FF6',

                                          'MG6',

                                          'CLD',

                                          'TEP',

                                          'MP1',

                                          'MP2',

                                          'MP3',

                                          'MP4',

                                          'MP5',

                                          'MP6',

                                          'MP7',

                                          'MP8',

                                          'MP9',

                                          'UM1',

                                          'UM2',

                                          'UM3',

                                          'UM4',

                                          'UM5',

                                          'UM6',

                                          'UM7',

                                          'UM8',

                                          'UM9',

                                          'DIV')

    and cad.account_number NOT LIKE '%99%' AND cad.ann_review_month > 0 AND cad.investment_authority_id IN (2,3)

    group by aad.account_number

     

    I get the following back:

    11 7

    11 14

    12 7

    15 7

    15 14

    16 7

    16 14

    17 7

    19 7

    21 7

    21 14

    22 7

    22 14

    23 7

    23 14

    24 7

    24 14

    25 7

    31 7

    31 14

    31 21

    32 7

    32 14

    32 21

    33 7

    33 14

    33 21

    34 7

    34 14

    41 7

    41 14

    41 21

    42 7

    42 14

    43 7

    43 14

    43 21

    44 7

    44 14

    44 21

    47 7

    49 7

    50 7

    51 7

    52 7

    61 7

    61 14

    62 7

    62 14

    62 21

    64 7

    65 7

    65 14

    65 21

    67 7

    67 14

    69 7

    69 14

    69 21

    71 7

    71 14

    75 7

    78 7

    89 7

    89 14

    90 7

    91 7

    91 14

    92 7

    93 7

    94 7

    95 7

    97 7

    98 7

    99 7

    99 14

    how come some show like 99 twice, i want it to comebine the two numbers

     

     

  • Do you want it by distinct account numbers or by the distinct substring?  For the former go with Terry's solution.  For the latter, change the Group by to:

    Group by substring(ad.account_number,1,2)

  • First of all, you query is to heavy !

    You use 3 objects and only join two of them, leaving the 3-th one for crossjoining ???

    select substring(ad.account_number,1,2)as AcctPreFix, count(*) as Total

    from annual.annual_account_detail aad

    inner join common.common_account_detail cad

    on aad.ar_id = cad.account_id and cad.account_id

    inner join annual.annual_status aas

    on ????

    group by substring(ad.account_number,1,2)as AcctPreFix

     

    Only counting distinct account_numbers

    select substring(account_number,1,2)as AcctPreFix, count(*) as Total

    from

    (select ad.account_number

    from annual.annual_account_detail aad

    inner join common.common_account_detail cad

    on aad.ar_id = cad.account_id and cad.account_id

    inner join annual.annual_status aas

    on ????

    group by ad.account_number

    ) NestedTE

    group by substring(account_number,1,2)as AcctPreFix

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • appreciate all the great advice, new to all the sql stuff, working on modifying some of the code, and will let you know the results, thanks for spending the time and helping me out.  the third table i am not sure why i had it in there , anyways, thanks again

Viewing 14 posts - 1 through 13 (of 13 total)

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