Home » SQL & PL/SQL » SQL & PL/SQL » two different counts in select
two different counts in select [message #684549] |
Mon, 28 June 2021 12:19 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi Team,
I want to fetch the count from two different tables based on the one key field.
I wrote the logic but couldn't get the proper results.
Below is the scenario:
CREATE TABLE Persons (
PersonID int,
Name varchar(255),
Address varchar(255),
City varchar(255)
);
INSERT INTO Persons VALUES (1006714, 'JAMES', 'texas', 'texas');
INSERT INTO Persons VALUES (1006720, 'kyle', 'texas', 'texas');
INSERT INTO Persons VALUES (1005619, 'Steaven', 'texas', 'texas');
INSERT INTO Persons VALUES (1003812, 'Thomas', 'texas', 'texas');
CREATE TABLE REQ_TBL (
REQ_ID int,
Status varchar(255),
created_by varchar(255)
);
INSERT INTO REQ_TBL VALUES ('REQ1245', 'OPEN', 1006714);
INSERT INTO REQ_TBL VALUES ('REQ1246', 'APPROVED', 1006714);
INSERT INTO REQ_TBL VALUES ('REQ1247', 'CLOSED', 1006714);
INSERT INTO REQ_TBL VALUES ('REQ1248', 'PENDING', 1006714);
INSERT INTO REQ_TBL VALUES ('REQ1249', 'OPEN', 1006714);
INSERT INTO REQ_TBL VALUES ('REQ1250', 'APPROVED', 1006714);
CREATE TABLE PO_TBL (
PO_ID int,
Status varchar(255),
created_by varchar(255)
);
INSERT INTO PO_TBL VALUES ('PO6675', 'OPEN FOR PROCESSING', 1006714);
INSERT INTO PO_TBL VALUES ('PO6676', 'APPROVED FINALLY', 1006714);
INSERT INTO PO_TBL VALUES ('PO6677', 'CLOSED WITH', 1006714);
INSERT INTO PO_TBL VALUES ('PO6678', 'OPEN FOR PROCESSING', 1006714);
INSERT INTO PO_TBL VALUES ('PO6679', 'OPEN FOR PROCESSING', 1006714);
INSERT INTO PO_TBL VALUES ('PO6680', 'CLOSED WITH', 1006714);
expected OUTPUT:
PersonID OPEN_REQ OPEN_PO COUNT(REQ_ID) COUNT(PO_ID)
1006714 YES YES 5 4
1006714 NO NO 1 2
below is the SQL i have tried, but results are not correct.
SELECT A.PersonID, CASE WHEN B.Status IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END,
CASE WHEN C.Status IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END,
COUNT(B.REQ_ID), COUNT(C.PO_ID)
FROM
PERSONS A,
REQ_TBL B,
PO_TBL C
WHERE A.PERSONID = 1006714
AND A.PERSONID = B.CREATED_BY
AND A.PERSONID = C.CREATED_BY
GROUP BY A.PersonID, CASE WHEN B.Statua IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END,
CASE WHEN C.Statua IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END
Request you please help me with correct SQL.
Thank you.
Regards
Sekhar
|
|
|
|
|
|
Re: two different counts in select [message #684554 is a reply to message #684552] |
Tue, 29 June 2021 00:29 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Shouting in the dark:
SQL> with
2 req as (
3 select CREATED_BY,
4 CASE WHEN Status IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END OPEN_REQ,
5 count(*) cnt_req
6 from REQ_TBL
7 group by CREATED_BY,
8 CASE WHEN Status IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END
9 ),
10 po as (
11 select CREATED_BY,
12 CASE WHEN Status IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END OPEN_PO,
13 count(*) cnt_po
14 from PO_TBL
15 group by CREATED_BY,
16 CASE WHEN Status IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END
17 )
18 select a.personid, open_req, open_po, cnt_req, cnt_po
19 from PERSONS A, req B, po C
20 where A.PERSONID = 1006714
21 AND A.PERSONID = B.CREATED_BY
22 AND A.PERSONID = C.CREATED_BY
23 and c.OPEN_PO = b.OPEN_REQ
24 order by 1, 2 desc
25 /
PERSONID OPE OPE CNT_REQ CNT_PO
---------- --- --- ---------- ----------
1006714 YES YES 5 4
1006714 NO NO 1 2
Of course, this may or may not give the correct result if there are no rows for some status.
|
|
|
Re: two different counts in select [message #684555 is a reply to message #684552] |
Tue, 29 June 2021 00:57 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi Michel,
Output of SQL should have the count of req_id's and count of po_id's created by person.
And this count should be based on status of Req_id and PO_IDs. When Status of Req_id is either of these
'OPEN', 'APPROVED', 'PENDING' then we should display output as YES and its count.
ANd for other values of status it should be NO and its count.
Similarly when Status of PO_ID is either of these 'OPEN FOR PROCESSING', 'APPROVED FINALLY' then we should display
output as YES and its count.
and for other values of status it should be NO and its count.
And there is no relation between REQ_TBL and PO_TBL, the only common field between these table is CREATED_BY field. And this CREATED_BY field is same as the PersonId in persons table.
Also as mentioned created new table with proper data type for REQ_ID and PO_ID respectively.
CREATE TABLE REQ_TBL (
REQ_ID VARCHAR(15),
Status varchar(255),
created_by varchar(255)
);
CREATE TABLE REQ_TBL (
REQ_ID VARCHAR(15),
Status varchar(255),
created_by varchar(255)
);
thanks for your response and hope this information is clear now about the requirement.
Regards
Sekhar
|
|
|
|
Re: two different counts in select [message #684558 is a reply to message #684556] |
Tue, 29 June 2021 03:17 |
|
Sekhar6617
Messages: 24 Registered: March 2021
|
Junior Member |
|
|
Hi Michel,
Thanks for the query. I think it is working as expected except small issue(explained below).
And there will always be a value for status and we will not see any rows without status value.
If REQ_TBL has the status values related to YES and no rows for status value related to NO.
And If PO_TBL has the status values related to both YES and NO then our query is showing the rows whose status
are same. But when i comment the "c.OPEN_PO = b.OPEN_REQ" criteria we are fetching the rows perfectly.
But looks like those seems to be dupliacte. Hence is there a way to get rid off this.
Below is the sample date craeted for reference.
INSERT INTO Persons VALUES (2000989, 'David', 'texas', 'texas');
INSERT INTO REQ_TBL VALUES ('REQ2100', 'OPEN', 2000989);
INSERT INTO REQ_TBL VALUES ('REQ2100', 'APPROVED', 2000989);
INSERT INTO REQ_TBL VALUES ('REQ2100', 'OPEN', 2000989);
INSERT INTO REQ_TBL VALUES ('REQ2100', 'PENDING', 2000989);
INSERT INTO PO_TBL VALUES ('PO9819', 'OPEN FOR PROCESSING', 2000989);
INSERT INTO PO_TBL VALUES ('PO9819', 'APPROVED FINALLY', 2000989);
INSERT INTO PO_TBL VALUES ('PO9819', 'CLOSED WITH', 2000989);
INSERT INTO PO_TBL VALUES ('PO9819', 'OPEN FOR PROCESSING', 2000989);
output with this above code:
PERSONID OPEN_REQ OPEN_PO CNT_REQ CNT_PO
---------- --------- ---------- ---------- ------
1006714 YES YES 5 4
1006714 NO NO 1 2
2000989 YES YES 4 3
But, here we are missing another row for 2000989.
when ever I commented "c.OPEN_PO = b.OPEN_REQ" then the output as below. So is there a way to get the distinct rows with all information.
since below output look like duplicate rows.
PERSONID OPEN_REQ OPEN_PO CNT_REQ CNT_PO
---------- --------- ---------- ---------- ------
1006714 YES NO 5 2
1006714 YES YES 5 4
1006714 NO NO 1 2
1006714 NO YES 1 4
2000989 YES NO 4 1
2000989 YES YES 4 3
Thank you.
Regards
Sekhar
|
|
|
|
|
Re: two different counts in select [message #684561 is a reply to message #684560] |
Tue, 29 June 2021 05:30 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
So you just want cases where "c.OPEN_PO = b.OPEN_REQ".
Here's a possibility:
SQL> col open_req format a8
SQL> col open_po format a7
SQL> with
2 cat as (select 'YES' cat from dual union all select 'NO' from dual),
3 req as (
4 select CREATED_BY,
5 CASE WHEN Status IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END OPEN_REQ,
6 count(*) cnt_req
7 from REQ_TBL
8 group by CREATED_BY,
9 CASE WHEN Status IN ('OPEN', 'APPROVED', 'PENDING') THEN 'YES' ELSE 'NO' END
10 ),
11 po as (
12 select CREATED_BY,
13 CASE WHEN Status IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END OPEN_PO,
14 count(*) cnt_po
15 from PO_TBL
16 group by CREATED_BY,
17 CASE WHEN Status IN ('OPEN FOR PROCESSING', 'APPROVED FINALLY') THEN 'YES' ELSE 'NO' END
18 )
19 select a.personid, cat open_req, cat open_po, nvl(cnt_req,0) cnt_req, nvl(cnt_po,0) cnt_po
20 from cat cross join PERSONS A
21 left outer join req B on A.PERSONID = B.CREATED_BY AND nvl(b.OPEN_REQ,'X') = cat
22 left outer join po C on A.PERSONID = C.CREATED_BY AND nvl(c.OPEN_PO,'X') = cat
23 where A.PERSONID in (1006714, 2000989)
24 order by 1, 2 desc
25 /
PERSONID OPEN_REQ OPEN_PO CNT_REQ CNT_PO
---------- -------- ------- ---------- ----------
1006714 YES YES 5 4
1006714 NO NO 1 2
2000989 YES YES 4 3
2000989 NO NO 0 1
|
|
|
|
Goto Forum:
Current Time: Sun Sep 29 03:50:44 CDT 2024
|