Oracle SQL Query for fetching SUM of Quarterly Sales through order date

 
Oracle SQL Query for fetching SUM of Quarterly Sales through order date




Oracle SQL Query for fetching SUM of Quarterly Sales through order date (metadata for practice is provided below and comment in for any better solutions on this query)


CREATE TABLE SALES(ORDERNO NUMBER(10),P_AMOUNT NUMBER(10,2), ORDERDATE DATE, CUST_ID NUMBER(10));


INSERT INTO SALES VALUES(70001,150.5,TO_DATE('2012-01-05','YYYY/MM/DD'),3005);

INSERT INTO SALES VALUES(70009,270.65,TO_DATE('2012-03-05','YYYY/MM/DD'),3001);

INSERT INTO SALES VALUES(70002,65.26,TO_DATE('2012-12-05','YYYY/MM/DD'),3002);

INSERT INTO SALES VALUES(70004,110.5,TO_DATE('2012-10-06','YYYY/MM/DD'),3009);

INSERT INTO SALES VALUES(70007,948.5,TO_DATE('2012-05-06','YYYY/MM/DD'),3005);

INSERT INTO SALES VALUES(70005,2400.6,TO_DATE('2012-10-07','YYYY/MM/DD'),3001);

INSERT INTO SALES VALUES(70008,5760,TO_DATE('2012-08-07','YYYY/MM/DD'),3002);

INSERT INTO SALES VALUES(70010,1983.43,TO_DATE('2012-07-07','YYYY/MM/DD'),3004);

INSERT INTO SALES VALUES(70003,2480.4,TO_DATE('2012-10-07','YYYY/MM/DD'),3008);



SELECT * FROM sales;

------------------------------------------------------------------------

ORDERNO P_AMOUNT ORDERDATE CUST_ID

70001 150.5 05-01-12 3005

70009 270.65 05-03-12 3001

70002 65.26 05-12-12 3002

70004 110.5 06-10-12 3009

70007 948.5 06-05-12 3005

70005 2400.6 07-10-12 3001

70008 5760 07-08-12 3002

70010 1983.43 07-07-12 3004

70003 2480.4 07-10-12 3008








SELECT TO_CHAR(orderdate,'Q') AS QUARTER,SUM(p_amount) AS "Total Sales"

FROM sales GROUP BY TO_CHAR(orderdate,'Q') ORDER BY QUARTER;


-----------------------------------

QUARTER Total Sales

1 421.15

2 948.5

3 7743.43

4 5056.76





Post a Comment

0 Comments