RE: help with query please ?
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
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Araujo, Daniel
Sent: Thursday, May 10, 2012 8:23 AM
To: Spokey Wheeler Gmail; Floyd Wellershaus
Cc: informix-list@iiug.org
Subject: RE: help with query please ?
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-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Spokey Wheeler Gmail
Sent: Wednesday, May 09, 2012 7:50 AM
To: Floyd Wellershaus
Cc: informix-list@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.
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
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
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.
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list