Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Analytic Functions with GROUP-BY Clause?

511898Apr 25 2009 — edited May 1 2009
I'm just getting acquainted with analytical functions. I like them. I'm having a problem, though. I want to sum up the results, but either I'm running into a limitation or I'm writing the SQL wrong. Any hints for me?

Hypothetical Table SALES consisting of a DAY_ID, PRODUCT_ID, PURCHASER_ID, PURCHASE_PRICE lists all the

Hypothetical Business Question: Product prices can fluctuate over the course of a day. I want to know how much per day I would have made had I sold one each of all my products at their max price for that day. Silly question, I know, but it's the best I could come up with to show the problem.

INSERT INTO SALES VALUES(1,1,1,1.0);
INSERT INTO SALES VALUES(1,1,1,2.0);
INSERT INTO SALES VALUES(1,2,1,3.0);
INSERT INTO SALES VALUES(1,2,1,4.0);
INSERT INTO SALES VALUES(2,1,1,5.0);
INSERT INTO SALES VALUES(2,1,1,6.0);
INSERT INTO SALES VALUES(2,2,1,7.0);
INSERT INTO SALES VALUES(2,2,1,8.0);

COMMIT;

Day 1: Iif I had sold one product 1 at $2 and one product 2 at $4, I would have made 6$.
Day 2: Iif I had sold one product 1 at $6 and one product 2 at $8, I would have made 14$.

The desired result set is:
DAY_ID                 MY_MEASURE
---------------------- ----------------------
1                        6
1                       14
The following SQL gets me tantalizingly close:
SELECT DAY_ID, 
  MAX(PURCHASE_PRICE) 
  KEEP(DENSE_RANK FIRST ORDER BY PURCHASE_PRICE DESC) 
  OVER(PARTITION BY DAY_ID, PRODUCT_ID) AS MY_MEASURE
  FROM SALES
ORDER BY DAY_ID
;

DAY_ID                 MY_MEASURE
---------------------- ----------------------
1                      2
1                      2
1                      4
1                      4
2                      6
2                      6
2                      8
2                      8
But as you can see, my result set is "longer" than I wanted it to be. I want a single row per DAY_ID. I understand what the analytical functions are doing here, and I acknowledge that I am "not doing it right." I just can't seem to figure out how to make it work.

Trying to do a sum() of max() simply does not work, nor does any semblance of a group-by clause that I can come up with. Unfortunately, as soon as I add the windowing function, I am no longer allowed to use group-by expressions (I think).

I am using a reporting tool, so unfortunately using things like inline views are not an option. I need to be able to define "MY_MEASURE" as something the query tool can apply the SUM() function to in its generated SQL.

(Note: The actual problem is slightly less easy to conceptualize, but solving this conundrum will take me much closer to solving the other.)

I humbly solicit your collective wisdom, oh forum.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 29 2009
Added on Apr 25 2009
5 comments
6,041 views