Thanks, Bill. I now see my problem. I was trying to count the number
of times a particular student id occurred (namely, once for each order).
My approach should have been to count the number of order ID's for each
student ID. It's funny, but both approaches actually work, but the
Witango search action is geared for counting the number of x for each y
, as opposed to just the number of y's.
Compare
select
count(a1.studentid) as numorders,
a1.studentid
from dbo.maydayShirts a1, dbo.tblStudents2003_4 b1
where b1.yog = '2004'
and b1.student_id = a1.studentid
group by a1.studentid
order by numorders desc
Which counts y's
With
SELECT
t1.Student_ID,
COUNT(m2.orderID) AS COUNT_orderID
FROM dbo.tblStudents2003_4 t1,dbo.maydayShirts m2
WHERE (t1.YOG='2004')
AND ((m2.studentid=t1.Student_ID))
GROUP BY t1.Student_ID
ORDER BY 2 desc
Which counts the number of x's per y.
I am grateful for an instructive sql lesson!
John Newsom
-----Original Message-----
From: Bill Downall [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 25, 2004 2:11 PM
To: [EMAIL PROTECTED]
Subject: RE: Witango-Talk: another sql puzzler
On 25 Mar 2004 at 15:57, Wilcox, Jamileh (HSC) wrote:
> John - have you tried changing the Select Type in a Search Action to
> Summary? I think Witango will do this.
Jamileh is right. Change it to "groups of summaries."
1) Drag tblStudents2003_4.studentID to "columns"
2) If a1 has a primary key column (which it should), drag that to
columns.
2.5) go to the Joins page and complete the join
3) Back on the select, drag the column from step "1)" down to "group by"
(or
whatever it's called)
4) Up in "columns", make sure that the column dragged in "2)" has
"COUNT" as its
aggregate function.
5) Drag your "count" column over to order by.
6) Go to the critera page and do your graduation yea condition
select
count(a1.studentid) as numorders,
a1.studentid
from dbo.maydayShirts a1, dbo.tblStudents2003_4 b1
where b1.yog = '2004'
and b1.studentid = a1.studentid
group by a1.studentid
order by numorders desc
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
