SQL & PL/SQL

I'm currently doing tests on Oracle, Postgres and MySQL.
However Oracle returns me "ORA-00934: group function is not allowed here" the same query works with the others.

The idea is to return First,Min,Max,Last prices for a particular day on a particular ID.

SELECT
  quotes.id,
  min(quotes.received_time) as opening_time,
  (SELECT ask_price FROM "price_quotes" WHERE id=quotes.id AND received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND received_time=min(quotes.received_time) ) AS opening_price,
  (SELECT received_time FROM "price_quotes" WHERE received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND id=quotes.id and ask_price=min(quotes.ask_price) LIMIT 1) as min_price_time,
  min(quotes.ask_price) as min_price,
  max(quotes.received_time) as closing_time, 
  (SELECT ask_price FROM "price_quotes" WHERE id=quotes.id AND received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND received_time=max(quotes.received_time)) as closing_price,
  (SELECT received_time FROM "price_quotes" WHERE received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND id=quotes.id and ask_price=max(quotes.ask_price) LIMIT 1) as max_price_time,
  max(quotes.ask_price) as max_price
FROM
  "price_quotes" quotes
WHERE
  quotes.received_time >= timestamp'2012-12-10 00:00:00' and quotes.received_time < timestamp'2012-12-11 00:00:00'
  AND quotes.id = 668792
GROUP BY quotes.id ORDER BY quotes.id ;

I've tried using HAVING and moving the query to the WHERE but no luck.


Here is the Create Table:

CREATE TABLE "price_quotes"
(
   id number(8),
   received_time timestamp, 
   mid_price float, 
   bid_price float,
   ask_price float,
   mid_yield float,
   bid_yield float,
   ask_yield float,
   mid_spread float,
   bid_spread float,
   ask_spread float,
   product_id number(8), 
   product_yield float,
   PRIMARY KEY ( id , received_time )
);

Data Sample:

INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 08:00:00', 103, 120, 110, 7, 8, 9, 2.100, 3.050, 4.28999, 29, 1.050);
INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 10:00:00', 99, 98, 100, 4, 2, 3, 0.100, 0.050, 0.28999, 24, 0.050);
INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 16:00:00', 100, 99, 101, 5, 3, 4, 0.200, 0.100, 0.29999, 25, 0.100);
INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 17:00:00', 10, 9, 11, 1, 2, 3, 0.210, 0.330, 0.99, 15, 1.100);

Oracle : 11.2.0.1.0

Edited by: 986853 on Feb 7, 2013 12:23 PM