help with query please ?

[Floyd Wellershaus]

I have a table called club_members. It has 3 columns first_name,last_name,club_name

Write a query to summarize how many clubs each student is in.  The Dean wants a concise report so consolidate it to show just the count of students based on the number of clubs they are in.

Sample report (your numbers and basic styling may differ)

students, clubs_per_student

600, 0

300, 1

200, 2

...

I've tried and can't get a handle around this one. Any help would be greatly appreciated.

Thanks,

floyd

Floyd Wellershaus
Dba/Sa Informix/Oracle/Linux/Aix

http://www.linkedin.com/in/floydwellershaus 
http://photos.fwellers.com
========================================================

[Ian Goddard]

Floyd Wellershaus wrote:
> I have a table called club_members. It has 3 columns
> first_name,last_name,club_name
>
> Write a query to summarize how many clubs each student is in. The Dean
> wants a concise report so consolidate it to show just the count of
> students based on the number of clubs they are in.
>
> Sample report (your numbers and basic styling may differ)
>
> students, clubs_per_student
>
> 600, 0
>
> 300, 1
>
> 200, 2
>
> ...
>
>
> I've tried and can't get a handle around this one. Any help would be
> greatly appreciated.

Do you mean something like

SELECT
COUNT(*) students, clubs_per_student
FROM
(SELECT first_name, last_name, COUNT(*) clubs_per_student
FROM club_members
GROUP BY 1,2 )
GROUP BY clubs_per_student
ORDER BY clubs_per_student

?

Note this assumes that there are no duplicate first_name, last_name,
club_name rows.

It might not be that concise a report if there are a lot of clubs as
clubs_per_student might have a lot of values.

--
Ian

The Hotmail address is my spam-bin. Real mail address is iang
at austonley org uk

[Spokey Wheeler Gmail]

Something like:

select first_name || last_name as wholename, count(*) as ccount
from club_members
group by 1
into temp tccount;

select count(wholename), ccount
from tccount
group by 2
order by 2;

Or you could do something like

select count(wholename), ccount
from (
select first_name || last_name as wholename, count(*) as ccount
from club_members
group by 1
)
group by 2
order by 2;

Not tested either one.

Not sure how you're going to cope with students who aren't in any clubs.

On 9 May 2012, at 13:11, Floyd Wellershaus wrote:

> I have a table called club_members. It has 3 columns first_name,last_name,club_name
>
> Write a query to summarize how many clubs each student is in. The Dean wants a concise report so consolidate it to show just the count of students based on the number of clubs they are in.
>
> Sample report (your numbers and basic styling may differ)
>
> students, clubs_per_student
>
> 600, 0
>
> 300, 1
>
> 200, 2
>
> ...
>
>
>
> I've tried and can't get a handle around this one. Any help would be greatly appreciated.
>

> Thanks,
> floyd
>
>
>
> Floyd Wellershaus
> Dba/Sa Informix/Oracle/Linux/Aix
>
> http://www.linkedin.com/in/floydwellershaus
> http://photos.fwellers.com
> ========================================================

> _______________________________________________
> Informix-list mailing list
> Inform...@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list

[Clive Eisen]

On 09/05/2012 13:50, Spokey Wheeler Gmail wrote:

> Not sure how you're going to cope with students who aren't in any clubs.

Nor two students sharing the same name

[Araujo, Daniel]

What about something like:

Select cm.first_name || cm.last_name as name,
(select count(cm2.club_id) from club_members cm2 where cm2.student _id = cm.student_id)
From club_members cm
Where cm.status = 'active'

Daniel Araujo
MIS/PEIMS Coordinator
Los Fresnos CISD
(956)233-6995
 
---------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: This email and any files transmitted with it are the property of the Los Fresnos Consolidated Independent School District, are confidential, and are intended solely for the use of the individual or entity to whom this email is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

-----Original Message-----
From: informix-l...@iiug.org [mailto:informix-l...@iiug.org] On Behalf Of Spokey Wheeler Gmail
Sent: Wednesday, May 09, 2012 7:50 AM
To: Floyd Wellershaus
Cc: inform...@iiug.org
Subject: Re: help with query please ?

Something like:

select first_name || last_name as wholename, count(*) as ccount from club_members group by 1 into temp tccount;

select count(wholename), ccount
from tccount
group by 2
order by 2;

Or you could do something like

select count(wholename), ccount
from (
select first_name || last_name as wholename, count(*) as ccount from club_members group by 1
)
group by 2
order by 2;

Not tested either one.

Not sure how you're going to cope with students who aren't in any clubs.

CONFIDENTIALITY NOTICE: This email & attached documents may contain confidential information. All information is intended only for the use of the named recipient. If you are not the named recipient, you are not authorized to read, disclose, copy, distribute or take any action in reliance on the information and any action other than immediate delivery to the named recipient is strictly prohibited. If you have received this email in error, do not read the information and please immediately notify sender by telephone to arrange for a return of the original documents. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. If you did not receive all pages listed or if pages are not legible, please immediately notify sender by phone.

[Kennedy, Randy]

With the assumption that the names are consistent for each student and club, I think this is what you are looking for.
I read this as you want a listing of how many students are in how many clubs.
The only caveat is with this one table, you cannot get the count of students in 0 clubs. You would have to check a student master table against this table to find the count of students not in any clubs.

SELECT Clubs, count(*) as Students
FROM (SELECT first_name||last_name, count(*) as Clubs FROM club_members GROUP by 1)
Group by 1
Order by 1

This will give a listing like:
Clubs Students
-------- -------------
1 300
2 200
Etc.
Etc.

Thanks,
Randy