Hello,
I need help on analytic query. I have a table like this:
CREATE TABLE SCPOMGR.U_CSS_COLORFAM
(
CLUSTER_NAME VARCHAR2(40 CHAR),
STYLE VARCHAR2(50 CHAR),
COLOR_FAM VARCHAR2(50 CHAR),
HistQty NUMBER
)
with the following data:
INSERT INTO U_CSS_COLORFAM (CLUSTER_NAME, Style, ColorFam, HistQty) VALUES ('AAA', 'S1', 'F1', 100);
INSERT INTO CLUSTER_NAME (CLUSTER_NAME, Style, ColorFam, HistQty) VALUES ('AAA', 'S1', 'F2', '50');
INSERT INTO AAA (CLUSTER_NAME, Style, ColorFam, HistQty) VALUES ('AAA', 'S2', 'F1', '30');
INSERT INTO AAA (CLUSTER_NAME, Style, ColorFam, HistQty) VALUES ('AAA', 'S2', 'F3', '20');
--------------------------------------------------
CLUSTER_NAME Style ColorFam HistQty
AAA S1 F1 100
AAA S1 F2 50
AAA S2 F1 30
AAA S2 F3 20
--------------------------------------------------
and I want a query to obtain for each cluster_name/ColorFam the sum of HistQty of the Style where the ColorFam exists.
For example:
ColorFam F1 exists in Style S1 and S2 so its StyleHistQty is 200.
ColorFam F2 exists in Style S1 so its StyleHistQty is 150.
ColorFam F3 exists in Style S2 so its StyleHistQty is 50.
--------------------------------------------------
CLUSTER_NAME ColorFam StyleHistQty
AAA F1 200
AAA F2 150
AAA F3 50
--------------------------------------------------
Thanks for your help.
SQL> drop table U_CSS_COLORFAM purge;
Table dropped.
SQL>
SQL> CREATE TABLE U_CSS_COLORFAM
2 (
3 CLUSTER_NAME VARCHAR2(40 CHAR),
4 STYLE VARCHAR2(50 CHAR),
5 COLOR_FAM VARCHAR2(50 CHAR),
6 HistQty NUMBER
7 );
Table created.
SQL>
SQL>
SQL> INSERT INTO U_CSS_COLORFAM (CLUSTER_NAME, Style, Color_Fam, HistQty) VALUES ('AAA', 'S1', 'F1', 100);
1 row created.
SQL> INSERT INTO U_CSS_COLORFAM (CLUSTER_NAME, Style, Color_Fam, HistQty) VALUES ('AAA', 'S1', 'F2', '50');
1 row created.
SQL> INSERT INTO U_CSS_COLORFAM (CLUSTER_NAME, Style, Color_Fam, HistQty) VALUES ('AAA', 'S2', 'F1', '30');
1 row created.
SQL> INSERT INTO U_CSS_COLORFAM (CLUSTER_NAME, Style, Color_Fam, HistQty) VALUES ('AAA', 'S2', 'F3', '20');
1 row created.
SQL>
SQL> select
2 cluster_name, color_fam, sum(style_tot)
3 from
4 (
5 select u.*,
6 sum(histqty) over ( partition by style ) as style_tot
7 from U_CSS_COLORFAM u
8 )
9 group by
10 cluster_name, color_fam;
CLUSTER_NAME COLOR_FAM SUM(STYLE_TOT)
---------------------------------------- -------------------------------------------------- --------------
AAA F2 150
AAA F1 200
AAA F3 50
3 rows selected.
SQL>