June 4, 2005 at 2:17 pm
I have a table that contains several columns. However, I am only concerned with two of them:
Table Name:
chart_of_accounts
Column Names:
account_number and description_1
The account_number is 12 characters.
I want to display each set of unique characters in position 2 thru 5 of the 12 character account_number along with the description_1 value (any description_1 value that matches the 4 character portion of the account_number)
I could have several rows for each unique portion of the number as shown below.
072115101000 Overhead
072115111000 Executive department
072115114000 Accounting department
072115115000 Special Events Department
072115116000 Development department
072115117000 Membership department
072115301000 Public relations department
072115304000 Salary allocation
072115811000 Salaries - Gift Shop Manager
072115881000 Salaries - Cafe Manager
072141001000 Salary allocation
072141001056 Typesetter
072142001000 Salary allocation
For example, for the rows above I would like to have only one row returned with the 2 thru 5 position of the account_number and any description from any row that has that value.
7211 Overhead
7214 Salary allocation
The following query will give me all rows in the table:
select right(left(account_number,5),4)as accnt_no, description_1 from chart_of_accounts
I tried the following query:
select right(left(account_number,5),4)as accnt_no, description_1 from chart_of_accounts
group by right(left(account_number,5),4)
which resulted in the following error:
Column 'chart_of_accounts.Description_1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any help in creating the query will be greatly appreciated.
Howard
June 4, 2005 at 3:10 pm
select right(left(account_number,5),4)as accnt_no,
min(description_1)
from chart_of_accounts
group by right(left(account_number,5),4)
June 4, 2005 at 3:15 pm
Thank you, that is exactly what I was looking for.
Question, why does the min(description_1) work and just description_1 not work?
June 4, 2005 at 3:16 pm
select substring(account_number,2,4) as accnt_no,
min(description_1)
from chart_of_accounts
(Edit) Group By substring(account_number,2,4)
June 4, 2005 at 3:23 pm
Slight modification to your last query:
select substring(account_number,2,4) as accnt_no,
min(description_1)
from chart_of_accounts
where left(right(account_number,5),2) = '20'
group by substring(account_number,2,4)
order by substring(account_number,2,4)
Works just fine.
Can you tell me why removing the 'min' around description_1 will cause an error?
June 4, 2005 at 3:24 pm
When using Group By all columns must be included in the Group By clause OR in an aggregate statement (min, max, sum etc.)
Aggregate statements are always applied at the group level. That is why you get the correct description per group.
When Group By is not included in the Select then the entire result set is by default considered the group and that is why min(), max(), sum() etc. appear to work on the entire result set when there is no Group By.
June 4, 2005 at 3:28 pm
Thank you.
You have been extremely helpful.
Howard
June 6, 2005 at 9:20 am
Thanks for the suggestion and example of a different schema design.
This is very helpful information.
Howard
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply