Need help with query

Question

Post reply

Need help with query

hmbtx

SSCarpal Tunnel

Points: 4664
More actions
June 4, 2005 at 2:17 pm

#362870

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.

account_no                   description_1

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

Viewing 8 posts - 1 through 7 (of 7 total)

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

ron k SSCertifiable Points: 6103 More actions · Answer 1

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)

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 2

Thank you, that is exactly what I was looking for.

Question, why does the min(description_1) work and just description_1 not work?

ron k SSCertifiable Points: 6103 More actions · Answer 3

select substring(account_number,2,4) as accnt_no,

min(description_1)

from chart_of_accounts

(Edit) Group By substring(account_number,2,4)

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 4

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?

ron k SSCertifiable Points: 6103 More actions · Answer 5

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.

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 6

hmbtx

SSCarpal Tunnel

Points: 4664

June 4, 2005 at 3:28 pm

#563085

Thank you.

You have been extremely helpful.

Howard

hmbtx SSCarpal Tunnel Points: 4664 More actions · Answer 7

Thanks for the suggestion and example of a different schema design.

This is very helpful information.

Howard