Re: SQL Brain Teaser
Date: 1995/09/23
Message-ID: <441fpp$dla_at_odin.community.net>#1/1
In article <43pm8d$2qr_at_atlas.bgers.co.uk>,
bainbridge_i_at_perc03_at_bgers.co.uk (Ian Bainbridge) wrote:
> I have a table Project_costs ( project_code, expenditure_code ......)
>
> There are 1-n items per project_code,expenditure_code combination.
>
> What I am trying to determine with one piece of SQL is the number of
> different expenditure_codes per project_code and ultimately the maximum
> of those counts returned.
>
> I know I could do a select distinct of project_code and then loop for
> each project_code find the number of distinct occurences of
expedniture_code
> and track the maximum of those myself but I just feel it should be
possible in
> one pass.
I'm not sure that I understand the problem but would
select project_code, count(distinct expenditure_code) from project_costs group by project_code;
do what you are looking for.
This should give the number of different expenditure_codes for each project_code. I'm not sure what maximum you're looking for.
Hope this helps,
Eric Received on Sat Sep 23 1995 - 00:00:00 CEST