Results 1 to 4 of 4
Thread: oracle sql syntax problem
-
05-25-2005, 05:42 AM #1Web Hosting Master
- 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)
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
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
-
05-25-2005, 05:46 AM #2Web Hosting Master
- 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 ;
cheers
-
05-26-2005, 01:11 AM #3Living the dream
- 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
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.
-
05-26-2005, 06:50 AM #4Web Hosting Master
- 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!