Re: Multiple Count's in one Select.
| From: | OKi98 | Date: | Fri, 05 Jan 2007 13:19:51 +0000 |
| Subject: | Re: Multiple Count's in one Select. | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-42888@lists.php.net to get a copy of this message | ||
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 DESCI 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