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