you can use: select messagestate, count(*) from table where uid=1 group by messagestate;
for uid=1, if you want to do the same for all users with one query you can use: select uid, messagestate, count(*) from table group by uid, messagestate; Regards, Ben Holness wrote: > Hi all, > > I have a table that has data that looks something like this (fixed font on): > > UserID MessageID MessageState > 1 1 PENDING > 1 2 PENDING > 2 3 FAILED > 3 4 DELIVERED > 2 5 ACCEPTED > 2 6 DELIVERED > 1 7 DELIVERED > > and so on. > > I am trying to work out a query that for a given UserID will tell me how > many messages are in which state, without having knowledge of the possible > states. > > Using the above data, a query on UserID 1 would return: > PENDING 2 > DELIVERED 1 > > a query on UserID 2 would return: > FAILED 1 > ACCEPTED 1 > DELIVERED 1 > > Is this possible and can anyone point me in the right direction to do it > please? I know how to get a count for all the pending messages for a > particular user ID (SELECT count(MessageID) WHERE UserID='1' AND > MessageState='PENDING'), but I am not sure how to get a new row for each > message type. > > Cheers, > > Ben -- George Chelidze --------------------------------------------------------------------- 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
