I added a having clause which got me in one statement all the students who had
registered exactly 31 requests!
SELECT P1.StuID, count(P1.stuID) FROM dbo.ProjectRequestsUnique P1 GROUP BY
P1.StuID HAVING count(p1.stuid) = 31
(all this to recover from accidently deleting 295 records because I hurried did
a search builder with delete functions without creating a unique ID field in my
table.) I'll never do that again. Foreign keys are great, but each table
should have a unique key as well for update and delete functions. Sometimes we
learn the hard way.
Thanks for all the quick help.
John
> the sql of tally... nicely done
> -----Original Message-----
> From: Atrix Wolfe <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> Date: Sunday, March 02, 2003 10:49 AM
> Subject: Re: Witango-Talk: searching for elegant count sql statement
>
>
> wait, take out the distinct and add studentname into the results, so you get
> this:
>
> sel studentname count(studentname) from tablename group by studentname
>
>
> ----- Original Message -----
> From: Atrix Wolfe
> To: [EMAIL PROTECTED]
> Sent: Sunday, March 02, 2003 10:42 AM
> Subject: Re: Witango-Talk: searching for elegant count sql statement
>
>
> this should do what your looking for:
>
> sel count(distinct studentname) from tablename group by studentname
>
> if i understand you correctly
> ----- Original Message -----
> From: Brent Skean
> To: [EMAIL PROTECTED]
> Sent: Sunday, March 02, 2003 10:23 AM
> Subject: Re: Witango-Talk: searching for elegant count sql statement
>
>
> oops, disregard that last one, I misread your question.
>
> Brent
>
> -----Original Message-----
> From: John Newsom <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
> Date: Sunday, March 02, 2003 10:08 AM
> Subject: Witango-Talk: searching for elegant count sql statement
>
>
> Hi, I have a database with a table which contains multiple
> student id's,
>
>
>
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
>
> +----------+------------------+------+-----+---------+----------------+
> | projID | int(11) | | | 0 | |
> | stuID | int(11) | | | 0 | |
> | rank | int(11) | | | 0 | |
> | uniqueID | int(10) unsigned | | MUL | NULL | auto_increment |
>
> +----------+------------------+------+-----+---------+----------------+
> 4 rows in set (0.00 sec)
>
> most occurring more than once since a student may make multiple
> requests which are stored in the table. I want to count the number of times each
> student name occurs, and then store the frequency in an array. I thought that
> the sql count function would help, but it seems not directly. I had to first do
> a search to find each unique id (using distinct), then with a for loop iterate
> through the resultset, and do a search for each id, and then add each numrows
> value to an array.
>
> I also tried bracketing the second search between two results
> html with a beginning and ending rows loop, but that doesn't seem to work
> either!
>
> So, short version is I have the thing working (see attached taf)
> but seems like a lot of work that should be able to be accomplished in a single
> select statement.
>
> Thanks for any insights.
>
> John Newsom
>
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
>
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
> with unsubscribe witango-talk in the message body
________________________________________________________________________
TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED]
with unsubscribe witango-talk in the message body
