Re: Multiple Count's in one Select.

[OKi98 <oki98@xxxxxxxxxx>]

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