Re: Summary query (Everquest related!)

BVince2172 wrote:

> I've spent far too long trying to work this SQL out, but I'm convinced there's
> a 'neat' solution to it.
>
> My dilemma is this, using the following tables:
>
> ECHELON
> ========
> echelon_id (number) (primary key)
> name (varchar2)
>
> CHARACTER
> =======
> character_id (number) (primary key)
> level (number)
> echelon_id
>
> Ok. Only for those echelons where there is at least 2 members (ie.
> COUNT(echelon_id) in Character is >= 2) I want to list the total number of
> characters in that echelon and how many characters in that echelon have levels
> in the the following ranges: <20 20 to 29 30 to 49 >=50
>
> I'm hoping to get results something like this:
>
> echelon_id name characters <20 20-29 30-49 >=50
> =============================================
> 1 GRACE 3 1 0 2 0
> 4 SHADOW 2 0 0 0 2
> 10 CONFLICT 10 2 5 1 2
>
> I've been bashing my head against a wall since I'm relatively new to this.
> Anyone give me a pointer?
>
> Thanks,
> Ben

Break the problem up into smaller pieces.

First ... find those that have two members. You can do this with a SQL statement that has GROUP BY and HAVING COUNT(*) > 2. This SQL statement becomes an in-line view.

Next a SQL statement with the count. And if you use the CASE statement (available as PL/SQL in 9i and with native dynamic SQL in 8i) you can easily create the ranges.

Then just put it all together with a third select statement joining the two described above.

Daniel Morgan Received on Thu Aug 22 2002 - 10:14:06 CDT