Re: [PHP-DB] Multiple Count's in one Select.

[Ed]

----- Original Message ----- 
From: "OKi98" <[EMAIL PROTECTED]>
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 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php