Hi! I need to make a select which is the combined result of 2 selects. I assume I could do this in v4 using UNION or in v3 by running 2 "select into" statements, however I'd prefer to do it in v3.23 and if possible with just one select and just read permissions.
The table is like this : mysql> desc process; +----------------+-----------------------+------+-----+----------------- ---- +-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-----------------------+------+-----+----------------- ---- +-------+ | timecode | datetime | | | 0000-00-00 00:00:00 | | | systemid | smallint(5) unsigned | | MUL | 0 | | | username | varchar(20) | | | | | | cpuusage | float(4,2) | YES | | NULL | | +----------------+-----------------------+------+-----+----------------- ---- +-------+ It's a table containing unix users and their cpu usage. The table contain more info about processnames memory usage per process etc. etc.. I first make a select to find the 2 users with the most CPU usage : SELECT username, sum(cpuusage) AS sum_cpuusage FROM process WHERE systemid = '2' AND timecode > now() - INTERVAL 1 DAY GROUP BY username ORDER BY sum_cpuusage DESC LIMIT 2 I then use the result from this select to make a new select with the format timecode, user1, user2, user3 etc... where user1, user2 etc. is the sum() of these users cpu usage at any given timecode. I use sum because every user can have more than one process active at any given time, and I store all active (cpu using) processes in the database. 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 ??? 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. /Jesper --------------------------------------------------------------------- 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
