Home » SQL & PL/SQL » SQL & PL/SQL » Multiple groupings in SQL (Oracle 11.2.0.3, Linux)
| Multiple groupings in SQL [message #601425] |
Wed, 20 November 2013 16:12  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi All,
I've below 3 tables. Can someone please help me out on writing sql to check multiple values in differet columns and do the grouping.
CREATE TABLE REF_ENTITY_TBL
(GROUP_CD NUMBER(2),
DEPT_CD NUMBER(2),
SUB_CD NUMBER(3),
PROD_ID NUMBER(10)
);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,1,29,1);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,24,609,2);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,29,119,3);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (1,15,537,12);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,503,4);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,537,5);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,20,918,6);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,22,921,7);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (2,22,876,8);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,50,655,9);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,50,676,10);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (3,77,697,11);
INSERT INTO REF_ENTITY_TBL (GROUP_CD, DEPT_CD, SUB_CD, PROD_ID) VALUES (4,98,537,13);
commit;
select * from ref_entity_tbl;
CREATE TABLE FCT_TXN_DATA
(TRT_ID NUMBER(10),
PROD_ID NUMBER(10),
TXN_AMT NUMBER(10),
TXN_ID NUMBER(10)
);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 1, 10, 1);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 2, 20, 2);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 1, 15, 3);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 10, 15, 10);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (100, 6, 15, 15);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 3, 30, 4);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 3, 50, 5);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 2, 25, 6);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 11, 25, 11);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 1, 20, 7);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 2, 45, 8);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 9, 25, 9);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 10, 15, 12);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 5, 15, 13);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 4, 10, 14);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 12, 20, 15);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 4, 30, 16);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (200, 5, 20, 17);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 5, 20, 17);
INSERT INTO FCT_TXN_DATA (TRT_ID, PROD_ID, TXN_AMT, TXN_ID) VALUES (300, 13, 25, 18);
commit;
select * from FCT_TXN_DATA;
CREATE TABLE REF_TRT_MAP_TBL
(TRT_ID NUMBER(10),
GROUP_CD VARCHAR2(20),
DEPT_CD VARCHAR2(20),
SUB_CD VARCHAR2(20)
);
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (100,'1,3',null,null );
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (200,2,77,null );
INSERT INTO REF_TRT_MAP_TBL (TRT_ID, GROUP_CD, DEPT_CD, SUB_CD) VALUES (300,3,24,537 );
COMMIT;
Based on the ref_trt_map_tbl table i need to select data from FCT_TXN_DATA and REF_ENTITY_TBL do the grouping on TRT_ID, GROUP_CD, DEPT_CD and SUB_CD
1) For TRT_ID 100, REF_TRT_MAP_TBL has only GROUP_CD populated so i need to select the data from FCT_TXN_DATA where the group_cd belongs to 1 and 3
2) For TRT_ID 200, REF_TRT_MAP_TBL has GROUP_CD, DEPT_CD populated, so i need to select all the DEPT_CD belongs to GROUP_CD 2
plus DEPT_CD 77
3) For TRT_ID 300, REF_TRT_MAP_TBL has GROUP_CD, DEPT_CD, SUB_CD populated, so i need to select all the DEPT_CD belongs to GROUP_CD 2
plus DEPT_CD 24 and in addition the records belongs to SUB_CD 537. (It could be multiple values in sub_cd field separated by comma)
Output
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- -------- ------- ------ ----------
100 1 45
100 3 15
200 2 20 50
200 77 25
300 24 45
300 537 60
300 3 40
I've written individual queries, but i've thousdand of trt_id's in my table, Can someone please help me out on making this generic.
Query for TRT_ID 300
SQL> select trt_id, case when group_cd = 3 then group_cd else null end as group_cd,
2 case when dept_cd = 24 then dept_cd else null end as dept_cd,
3 case when sub_cd = 537 then sub_cd else null end as sub_cd
4 ,sum(txn_amt) tot_amt
5 from FCT_TXN_DATA a, REF_ENTITY_TBL b
6 where a.prod_id = b.prod_id
7 and (b.group_cd = 3 or b.dept_cd = 24 or b.sub_cd = 537)
8 and a.trt_id = 300
9 group by trt_id, case when group_cd = 3 then group_cd else null end,
10 case when dept_cd = 24 then dept_cd else null end,
11 case when sub_cd = 537 then sub_cd else null end;
For TRT_ID 200
SQL> select trt_id, case when group_cd = 2 then group_cd else null end as group_cd,
2 case when dept_cd = 77 then dept_cd else null end as dept_cd,
3 null as sub_cd ,
4 sum(txn_amt) tot_amt
5 from FCT_TXN_DATA a, REF_ENTITY_TBL b
6 where a.prod_id = b.prod_id
7 and (b.group_cd = 2 or b.dept_cd = 77)
8 and a.trt_id = 200
9 group by trt_id, case when group_cd = 2 then group_cd else null end,
10 case when dept_cd = 77 then dept_cd else null end;
Appreciate your help!
Thanks
SS
|
|
|
|
|
|
| Re: Multiple groupings in SQL [message #601487 is a reply to message #601428] |
Thu, 21 November 2013 07:47  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Manu,
Sorry i didn't used ref_trt_map_tbl in my queries. I tried the below one but getting only one row. Did i miss anything?
select c.trt_id, case when c.group_cd is not null then c.group_cd else null end as group_cd,
case when c.dept_cd is not null then c.dept_cd else null end as dept_cd,
case when c.sub_cd is not null then c.sub_cd else null end as sub_cd ,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and (c.group_cd = b.group_cd or c.dept_cd = b.dept_cd)
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 200
group by c.trt_id, case when c.group_cd is not null then c.group_cd else null end,
case when c.dept_cd is not null then c.dept_cd else null end,
case when c.sub_cd is not null then c.sub_cd else null end;
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- -------------------- -------------------- -------------------- ----------
200 2 77 75
Appreicate your help!
Thanks
SS
|
|
|
|
| Re: Multiple groupings in SQL [message #601490 is a reply to message #601487] |
Thu, 21 November 2013 07:54 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
I used UNION ALL and getting results, but i'm not sure how much efficient it will be with the table (FCT_TXN_DATA) which has almost close 1 Billion records. Is there anyway i can avoid union all.
select c.trt_id, c.group_cd,null as dept_cd, null as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and c.group_cd = b.group_cd
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 200
group by c.trt_id, c.group_cd
union all
select c.trt_id, null as group_cd,c.dept_cd as dept_cd, null as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and c.dept_cd = b.dept_cd
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 200
group by c.trt_id, c.dept_cd;
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- -------------------- -------------------- ------ ----------
200 2 50
200 77 25
select c.trt_id, c.group_cd,null as dept_cd, null as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and c.group_cd = b.group_cd
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 300
group by c.trt_id, c.group_cd
union all
select c.trt_id, null as group_cd,c.dept_cd as dept_cd, null as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and c.dept_cd = b.dept_cd
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 300
group by c.trt_id, c.dept_cd
union all
select c.trt_id, null as group_cd, null as dept_cd, c.sub_cd as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and c.sub_cd = b.sub_cd
--(b.group_cd = 2 or b.dept_cd = 77)
and a.trt_id = 300
group by c.trt_id, c.sub_cd ;
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- -------------------- -------------------- -------------------- ----------
300 3 40
300 24 45
300 537 60
Thanks
SS
|
|
|
|
|
|
| Re: Multiple groupings in SQL [message #601532 is a reply to message #601523] |
Thu, 21 November 2013 22:48 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi Manu,
Thank you! I'm trying to write query but not getting expected results.
select c.trt_id, case when b.group_cd is not null then b.group_cd else null end as group_cd,
case when b.dept_cd is not null then b.dept_cd else null end as dept_cd,
case when c.sub_cd is not null then b.sub_cd else null end as sub_cd,
sum(txn_amt) tot_amt
from FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
where a.prod_id = b.prod_id
and a.trt_id = c.trt_id
and (
(c.group_cd is not null and b.group_cd in (select * from table(parse_list(c.group_cd))))
OR
(c.dept_cd is not null and b.dept_cd = c.dept_cd)
OR
(c.sub_cd is not null and b.sub_cd = c.sub_cd)
)
group by c.trt_id,case when b.group_cd is not null then b.group_cd else null end,
case when b.dept_cd is not null then b.dept_cd else null end,
case when c.sub_cd is not null then b.sub_cd else null end;
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- ---------- ---------- ---------- ----------
100 1 1 25
100 1 24 120
100 3 50 15
200 2 20 50
200 3 77 25
300 1 15 537 20
300 1 24 609 45
300 2 20 537 15
300 3 50 655 25
300 3 50 676 15
300 4 98 537 25
Thanks
SS
|
|
|
|
| Re: Multiple groupings in SQL [message #601598 is a reply to message #601532] |
Fri, 22 November 2013 12:26 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi SS,
I am also doing this for very first time. I think it should be something like below.
It's not complete and not working even, because I don't have parse_list function.
SELECT c.trt_id,
c.group_cd,
c.dept_cd,
c.sub_cd,
SUM (txn_amt) tot_amt
FROM FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
WHERE CASE
WHEN c.group_cd IS NOT NULL AND c.group_cd = b.group_cd THEN 1
WHEN c.dept_cd IS NOT NULL AND c.dept_cd = b.dept_cd THEN 1
WHEN c.sub_cd IS NOT NULL AND c.sub_cd = b.sub_cd THEN 1
ELSE 0
END = 1
AND a.prod_id = b.prod_id
AND a.trt_id = c.trt_id
GROUP BY c.trt_id,
c.group_cd,
c.dept_cd,
c.sub_cd;
And by looking at the parselist function, I will say you are breaking 1st normalization rule.
I will highly recommend, number must be stored as number. Now you are storing them as string and again parsing as number, very serious mistake btw.
Regards,
Manu
|
|
|
|
| Re: Multiple groupings in SQL [message #601600 is a reply to message #601598] |
Fri, 22 November 2013 13:17 |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
i'm using parse_list function to parse the values in group_cd field in ref_trt_map_tbl as we are putting multiple group_cd values separated by comma.
Here is the code for that function
create or replace type split_tbl as table of VARCHAR2(32767);
create or replace FUNCTION parse_list(p_list varchar2, p_del varchar2 := ',')
RETURN split_tbl
PIPELINED IS
l_idx pls_integer;
l_list varchar2(32767) := p_list;
begin
loop
l_idx := instr(l_list, p_del);
if l_idx > 0 then
pipe row(substr(l_list, 1, l_idx - 1));
l_list := substr(l_list, l_idx + length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
END parse_list;
Thanks
SS
|
|
|
|
| Re: Multiple groupings in SQL [message #601601 is a reply to message #601600] |
Fri, 22 November 2013 13:31 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
OK, here is code, but giving expected result but in one row, you have to correct group by clause now.
SELECT c.trt_id,
c.group_cd,
c.dept_cd,
c.sub_cd,
SUM (txn_amt) tot_amt
FROM FCT_TXN_DATA a, REF_ENTITY_TBL b, REF_TRT_MAP_TBL c
WHERE CASE
WHEN c.group_cd IS NOT NULL AND b.group_cd in (select * from table(parse_list(c.group_cd))) THEN 1
WHEN c.dept_cd IS NOT NULL AND c.dept_cd = b.dept_cd THEN 1
WHEN c.sub_cd IS NOT NULL AND c.sub_cd = b.sub_cd THEN 1
ELSE 0
END = 1
AND a.prod_id = b.prod_id
AND a.trt_id = c.trt_id
and c.trt_id = 300
GROUP BY c.trt_id,
c.group_cd,
c.dept_cd,
c.sub_cd;
Try, and if you can.
Regards,
Manu
[Updated on: Fri, 22 November 2013 13:31] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Multiple groupings in SQL [message #602150 is a reply to message #601425] |
Sat, 30 November 2013 04:09 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
First of all, your table REF_TRT_MAP_TBL is not normalized. You should never keep '1,3' together in the GROUP_CD. It must be two different rows. By doing the number of rows in the expected output will also vary.
Modify the DDL to :
CREATE TABLE ref_trt_map_tbl
(
trt_id NUMBER(10),
group_cd NUMBER(2),
dept_cd NUMBER(2),
sub_cd NUMBER(3)
);
INSERT INTO ref_trt_map_tbl
(trt_id,
group_cd,
dept_cd,
sub_cd)
VALUES (100,
1,
NULL,
NULL );
INSERT INTO ref_trt_map_tbl
(trt_id,
group_cd,
dept_cd,
sub_cd)
VALUES (100,
3,
NULL,
NULL );
INSERT INTO ref_trt_map_tbl
(trt_id,
group_cd,
dept_cd,
sub_cd)
VALUES (200,
2,
77,
NULL );
INSERT INTO ref_trt_map_tbl
(trt_id,
group_cd,
dept_cd,
sub_cd)
VALUES (300,
3,
24,
537 );
COMMIT;
I went throught the entire thread, however, I couldn't understand your exact requirement. How in your output is the sum of TXN_AMT being calculated?
SQL> SELECT FTD.TRT_ID,
2 RTM.GROUP_CD,
3 RTM.DEPT_CD,
4 RTM.SUB_CD,
5 SUM(CASE
6 WHEN RTM.GROUP_CD IS NULL OR RTM.DEPT_CD IS NULL THEN
7 NULL
8 ELSE
9 FTD.TXN_AMT
10 END) TOT_AMT
11 FROM FCT_TXN_DATA FTD, REF_TRT_MAP_TBL RTM, REF_ENTITY_TBL RET
12 WHERE FTD.TRT_ID = RTM.TRT_ID
13 AND FTD.PROD_ID = RET.PROD_ID
14 GROUP BY FTD.TRT_ID, RTM.GROUP_CD, RTM.DEPT_CD, RTM.SUB_CD
15 UNION ALL
16 SELECT FTD.TRT_ID,
17 RET.GROUP_CD,
18 RET.DEPT_CD,
19 RET.SUB_CD,
20 SUM(FTD.TXN_AMT) TOT_AMT
21 FROM FCT_TXN_DATA FTD, REF_TRT_MAP_TBL RTM, REF_ENTITY_TBL RET
22 WHERE FTD.TRT_ID = RTM.TRT_ID
23 AND FTD.PROD_ID = RET.PROD_ID
24 GROUP BY FTD.TRT_ID, RET.GROUP_CD, RET.DEPT_CD, RET.SUB_CD
25 ORDER BY 1, 2;
TRT_ID GROUP_CD DEPT_CD SUB_CD TOT_AMT
----------- ---------- ---------- ---------- ----------
100 1
100 1 1 29 50
100 1 24 609 40
100 2 20 918 30
100 3
100 3 50 676 30
200 1 29 119 80
200 1 24 609 25
200 2 77 180
200 2 20 537 20
200 2 20 503 30
200 3 77 697 25
300 1 24 609 45
300 1 1 29 20
300 1 15 537 20
300 2 20 503 10
300 2 20 537 35
300 3 50 676 15
300 3 24 537 195
300 3 50 655 25
300 4 98 537 25
21 rows selected
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 16:20:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
