Thanks for the tip, I solved it a little differently - sql1="select f_name, l_name, count(*) as 'Attendance' from kids group by l_name"; sql2="select count(distinct kids_id) as 'total' from kids";
I added a new column to the database called kids_id, so when a new kid is added he/she gets assigned a number, if a record is added for an existing kid the new record keeps the existing kids_id, making it simple to count only distinct kids_id's. The input form lists the kids_id, f_name, l_name as a link to edit their record, and delete for a delete link, in the left column, and in the right is the form for input/editing. The kids list is ordered by kids_id, making it easy to know what id's already exist, or don't yet. -- Chip On Sat, 2002-09-21 at 01:39, Ignatius Reilly wrote: > This is a query requiring two different levels of aggregation. MySQL does > not support it. > > You can reduce it to two different queries > > SELECT f_name, l_name, COUNT(*) AS Total_Kids > FROM kids > GROUP BY l_name > > and > > SELECT COUNT(*) AS Total_Rows > FROM kids > > your result will have columns $row1['Total_Kids'] and $row2['Total_Rows'] > > When MySQL supports subqueries, you can write: > > SELECT f_name, l_name, COUNT(*) AS Total_Kids > FROM kids > GROUP BY l_name > JOIN > SELECT COUNT(*) AS Total_Rows > FROM kids > > HTH > Ignatius > ____________________________________________ > ----- Original Message ----- > From: "Chip Wiegand" <[EMAIL PROTECTED]> > To: "phpdb" <[EMAIL PROTECTED]> > Sent: Saturday, September 21, 2002 7:18 AM > Subject: [PHP-DB] how do I echo this statement to the browser? > > > > I want to get the first name, last name, total row count, and total time > > a name appears in the database. Something like this - > > > > select f_name, l_name, count(*) as 'Total Kids', count distinct l_name > > from kids group by l_name > > > > it doesn't work of course, the count distinct l_name part is completely > > wrong. The database has kids names in it appearing multiple times, > > whence the need for distinct, I want the total number of times it > > appears though. Plus, how do I echo the number for 'Total Kids' on the > > browser? I don't have a column heading to reference like with f_name and > > l_name. > > > > I s'pose this is clear as mud. > > > > Thanks for any help you all can provide, > > Chip > > > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
