Re: Multiple Count's in one Select.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux