----- Original Message -----
From: "OKi98" <oki98@xxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Friday, January 05, 2007 1:19 PM
Subject: Re: 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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
