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

Reply via email to