RE: How to combine two selects into one ???

[Jesper Frank Nemholt]

> -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 ???

[Benjamin Pflugmann]

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