Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    UK
    Posts
    2,569

    oracle sql syntax problem

    Hi

    ive got 2 tables:

    Code:
    SQL> desc machine_stats;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     HOST                                      NOT NULL NUMBER(6)
     TIMESTAMP                                          DATE
     MB                                                 NUMBER(3)
     CPU0                                               NUMBER(3)
     CPU1                                               NUMBER(3)
     CPU2                                               NUMBER(3)
     CPU3                                               NUMBER(3)
    
    SQL> desc host;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     HOST                                      NOT NULL NUMBER(6)
     HOSTTYPE                                  NOT NULL VARCHAR2(6)
     PLATFORM                                  NOT NULL VARCHAR2(3)
     HOSTNAME                                  NOT NULL VARCHAR2(50)
     LIVE                                      NOT NULL CHAR(1)
     MODEL                                              VARCHAR2(10)
     RACK                                               VARCHAR2(20)
    and i want to select the following information:

    Code:
      1  select h.host,h.hostname,h.model,h.rack,avg(m.mb),max(m.mb),avg(m.cpu0),max(m.cpu0),
      2  avg(m.cpu1),max(m.cpu1),avg(m.cpu2),max(m.cpu2),avg(m.cpu3),max(m.cpu3)
      3  from host h, machine_stats m
      4  where h.host = m.host
      5  and timestamp >= TO_DATE('00:00 20050524','HH24:MI YYYYMMDD')
      6  and timestamp <= TO_DATE('23:59 20050524','HH24:MI YYYYMMDD')
      7  and h.live = 'Y'
      8 group by h.host
    but nomatter what combination of selects/groups i cant seem to get the hostname,model and rack returned as i keep getting errors similar to 'not a GROUP BY expression'

    can anyone point out what im doing wrong? im sure its straightforward but i havent got a second pair of eyes that can check it here to point out my idiocies!

    Thanks in advance

  2. #2
    Join Date
    Apr 2003
    Location
    UK
    Posts
    2,569
    Code:
    SQL> select host,avg(mb),max(mb),avg(cpu0),max(cpu0),avg(cpu1),max(cpu1),avg(cpu2),max(cpu2),avg(cpu3),max(cpu3)
      2  from machine_stats
      3  where timestamp >= TO_DATE('00;00 20050524','HH24:MI YYYYMMDD')
      4  and timestamp <= TO_DATE('23:59 20050524','HH24:MI YYYYMMDD')
      5  group by host
      6  ;
    this worked, but now i'd like to add the info from the host table without doing a second query

    cheers

  3. #3
    Join Date
    May 2005
    Location
    Planet Earth
    Posts
    813
    Code:
    SELECT
     h.host,h.hostname,h.model,h.rack,avg(m.mb),max(m.mb),avg(m.cpu0),max(m.cpu0), avg(m.cpu1),max(m.cpu1),avg(m.cpu2),max(m.cpu2),avg(m.cpu3),max(m.cpu3)
    FROM
     host h, machine_stats m
    WHERE
     h.host = m.host
    AND timestamp >= TO_DATE('00:00 20050524','HH24:MI YYYYMMDD')
    AND timestamp <= TO_DATE('23:59 20050524','HH24:MI YYYYMMDD')
    AND h.live = 'Y'
    GROUP BY
     h.host, h.hostname, h.model, h.rack
    this should work.

    When using GROUP BY you should add all columns which are not inside a GROUP BY function avg(), max(), round(), etc.. listed.

    If it is still not working PM me I'll check it out with you.

    Sincerely
    PutFile.io — Disrupting traditional file hosting.
    █ Signup Early and enjoy Unlimited space/bandwidth for your files hosting, Forever!
    █ No Ads.
    █ No Countdowns.

  4. #4
    Join Date
    Apr 2003
    Location
    UK
    Posts
    2,569
    looked good, thanks for that

    by the time i read this this morning i'd already done it in a 2 part extraction and it works (and looks) v.v. nice

    thanks for the help i might change it in the near future if i get any spare time!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •