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

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

ETLGuru

1 год назад

1,917 Просмотров

Ссылки и html тэги не поддерживаются


Комментарии:

@hasanmougharbel8030
@hasanmougharbel8030 - 09.09.2022 22:19

Hey man, glad to meet again.
I have only a simple enquiry as a new sql learner.
How order by clause differs from rank function in sql?
Thanks for taking care of this.

Ответить
@etlguru
@etlguru - 04.09.2022 09:42

Meta Data for practice
------------------------------------

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

Ответить