As this is a new db i've not done any indexing.
There's only 4 rows in the table.
SELECT DISTINCT u.user_id, u.username, t.user_id, t.jobtype, (SELECT
COUNT(jobtype) FROM taskinput) AS 'COUNT', (SELECT COUNT(jobtype) FROM
taskinput WHERE t.jobtype = 'Navision') AS 'Navision', (SELECT
COUNT(jobtype) FROM taskinput WHERE t.jobtype = 'Abuse') AS 'Abuse', (SELECT
COUNT(jobtype) FROM taskinput WHERE t.jobtype = 'Tickets') AS 'Tickets',
(SELECT COUNT(jobtype) FROM taskinput) AS 'MegaTotals' FROM users u,
taskinput t WHERE t.user_id = u.user_id GROUP BY u.user_id, t.user_id;
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
| user_id | username | user_id | jobtype | COUNT | Navision | Abuse |
Tickets | MegaTotals |
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
| 1 | edd | 1 | Tickets | 4 | 0 | 0
| 0 | 4 |
| 10 | administrator | 10 | Tickets | 4 | 0 | 0 |
4 | 4 |
+---------+---------------+---------+---------+-------+----------+-------+---------+------------+
2 rows in set (0.00 sec)
Notice how i've removed the WHERE t.user_id = u.user_id from each SELECT
count? it's flown through but seems to be putting all the same results in
each count.
It takes far too long to get any kind of result where adding the extra WHERE
t.user_id = u.user_id AND t.jobtype = 'Tickets'
It's got me stumped, but then i've not slept in nearly 24 hours.
Thanks for your help so far :-)
----- Original Message -----
From: "Chris" <dmagick@xxxxxxxxx>
To: "Ed" <ed@xxxxxxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Friday, January 05, 2007 5:03 AM
Subject: Re: Multiple Count's in one Select.
Ed wrote:
Hi,
I've been expermenting with displaying stats for our intranet and I'm
looking at making it more robust so it can display stats better.
I've come up with the most ugly! long winded SQL statement You could
imagine.
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've never down anything so long in all the time i've been playing and
it's causing my CPU to go crazy although i'm only using 126mb of my 2.5GB
of RAM.
The query in question is fine if I strip out the WHERE t.user_id =
u.user_id from each SELECT COUNT it goes through in less than a second in
fact but doesnt display the results properly it gives every user the same
listings. Surely there must be a more efficent way of doing this? but
i've not been able to find one that executes quickly.
What indexes do you have?
Do you have one on
taskinput(user_id)
and
users(user_id) ?
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
