RE: How to combine two selects into one ???
> -Original Message-
> From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]]
> Sent: sábado, 25 de enero de 2003 21:38
> To: Jesper Frank Nemholt
> Cc: [EMAIL PROTECTED]
> Subject: Re: How to combine two selects into one ???
>
[clip]
> 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
> WHEREsystemid = 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.
Thanks, it did exactly what I wanted :-)
The other example you listed has the problem that my application, or
rather the PHP script that generates the graphics, only works if the
output is organized as one timecode followed by a number of values for
each row.
/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
Re: How to combine two selects into one ???
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
WHEREsystemid = 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
WHEREsystemid = 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
How to combine two selects into one ???
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 | | | -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
