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

Reply via email to