Re: SQL Brain Teaser
Date: 1995/09/26
Message-ID: <4489bo$1pb_at_ixnews3.ix.netcom.com>#1/1
bainbridge_i_at_perc03_at_bgers.co.uk (Ian Bainbridge) wrote:
> Can anybody lend a hand to this brain teaser because I seem to have
> developed brain death ?
> 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.
> Am I just off the planet today ?
> Any/all help/suggestions gratefully received.
>--
Is this what you are looking for?
select project_code, count(distinct expenditure_code)
from project_costs
group by project_code;
select project_code, count(distinct expenditure_code)
from project_costs
group by project_code
having count(distinct expenditure_code)
- (select max(count(distinct expenditure_code)) from project_costs group by project_code);