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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply