On Sat 2003-01-25 at 14:11:06 +0100, [EMAIL PROTECTED] wrote:
[...]
> What I want is the combined output of selects like these (example with 2
> users) :
>
> SELECT timecode,
> sum(cpuusage) AS jfn
> FROM process
> WHERE systemid = '2'
> AND username = 'jfn'
> AND timecode > now() - INTERVAL 1 DAY
> GROUP BY timecode
> ORDER BY timecode
>
> SELECT timecode,
> sum(cpuusage) AS root
> FROM process
> WHERE systemid = '2'
> AND username = 'root'
> AND timecode > now() - INTERVAL 1 DAY
> GROUP BY timecode
> ORDER BY timecode
>
> With combined I mean that I want it in the form "timecode, jfn, root".
>
> How do I rewrite this into one select, and can it be done without having
> to use features only present in v4, or features that require write
> access to the database ???
If I understand you correcty, the following should do what you
want (untested):
SELECT timecode,
SUM(IF(username='jfn',cpuusage,0)),
SUM(IF(username='root',cpuusage,0))
FROM process
WHERE systemid = 2 AND
timecode > now() - INTERVAL 1 DAY AND
username in ('jfn', 'root')
GROUP BY timecode
ORDER BY timecode
Note that it will be somewhat slower than two seperate queries, I
think.
> For people who want to test/try the real database, there's a read only
> web interface at http://statdb.dassic.com/sql.php
> The 2 queries from the example that I want to combine into one produce
> useful data, so the question is just to merge them into one, since the
> goal is to later, using another interface, ask a PHP script to draw a
> graph with a top 5 of CPU usage among users. To do this I need a output
> like this :
> timecode, user1, user2, user3 etc.
For that application, I do not see why the output of something like
SELECT timecode, user, SUM(cpuusage)
FROM process
WHERE systemid = 2 AND
timecode > now() - INTERVAL 1 DAY AND
username in ('jfn', 'root')
GROUP BY timecode, user
ORDER BY timecode, user
is not enough. That is easily post-processed in the application.
HTH,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
