On Tue, 2002-01-22 at 22:40, DL Neil wrote: > Hi Jonas, > > > I wonder if its possbile to make a toplist from the folowing table: > > > > +----------+----------+ > > | name | status | > > +----------+----------+ > > | Bob | working | > > | Jane | sleeping | > > | Joe | sleeping | > > | Bob | sleeping | > > | Bob | working | > > | Bob | sleeping | > > | Bob | working | > > | Jane | working | > > | Joe | sleeping | > > | Joe | working | > > | Bob | working | > > | Bob | working | > > +----------+----------+ > > > > I would like the toplist look something like this: > > > > +-------+---------+----------+ > > | name | working | sleeping | > > +-------+---------+----------+ > > | Bob | 5 | 2 | > > | Jane | 1 | 1 | > > | Joe | 1 | 2 | > > +-------+---------+----------+ > > > Yes it is possible. > Start with a SELECT on the table. > Use GROUP BY to organise the rows by name. > Use SUM to add the number of records. > Use IF to only sum when status="working", and in the next column when >status="sleeping".
Like this: SELECT name,sum(if(status='sleeping',1,0)) as sleeping, sum(if(status='working',1,0)) as working FROM tab1 GROUP BY name; Thanx to DL Neil, -- Diana Soares (sql) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
