Re: Multiple Count's in one Select.

----- Original Message ----- From: "OKi98" <oki98@centrum.cz> To: <php-db@lists.php.net> Sent: Friday, January 05, 2007 1:19 PM Subject: Re: [PHP-DB] Multiple Count's in one Select.

Ed wrote:

SELECT DISTINCT u.*, t.*, (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id ) AS 'COUNT', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Navision') AS 'Navision', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Abuse'') AS 'Abuse', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id AND t.jobtype = 'Tickets') AS 'Tickets', (SELECT COUNT(jobtype) FROM taskinput WHERE t.user_id = u.user_id) AS 'MegaTotals' FROM users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id, t.user_id ORDER BY COUNT DESC

I quess you are wrongly using aliases - try this and let me know: SELECT DISTINCT u.*, t.*, (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id) AS 'COUNT', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Navision') AS 'Navision', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Abuse'') AS 'Abuse', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id AND jobtype = 'Tickets') AS 'Tickets', (SELECT COUNT(jobtype) FROM taskinput WHERE user_id=u.user_id) AS 'MegaTotals' FROM users u, taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id, t.user_id ORDER BY COUNT DESC OKi98 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

Thank you very much! that seemed todo the trick nicely :) Thank you everyone who helped you've been very helpful. Ed