OraFAQ Forum: SQL & PL/SQL » two different counts in select

Home » SQL & PL/SQL » SQL & PL/SQL » two different counts in select

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

two different counts in select [message #684549]

Mon, 28 June 2021 12:19

Sekhar6617
Messages: 22
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

Report message to a moderator

Re: two different counts in select [message #684550 is a reply to message #684549]

Mon, 28 June 2021 12:58

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I wrote the logic

So could you share it with us?
It is hard to build a query without its specification.

Report message to a moderator

Re: two different counts in select [message #684551 is a reply to message #684550]

Mon, 28 June 2021 20:45

Sekhar6617
Messages: 22
Registered: March 2021

Junior Member

Hi Michel,

I want to display all the REQs and POs created by a particular person and it should be in one row. And based on the status of the REQ and PO we get a maximum of two rows per person. Without PO_TBL in the sql, results are good. But when ever we add PO_TBL to the sql to have the PO Count then results are correct. Tried it different ways also. Hence am looking for your suggestion on the logic. Hope I am clear on the requirement. Thank you.

Regards
Sekhar

Report message to a moderator

Re: two different counts in select [message #684552 is a reply to message #684551]

Tue, 29 June 2021 00:09

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

And based on the status of the REQ and PO we get a maximum of two rows per person.

Do you think that this information is sufficient to write a query?
What "status"?
Explain IN DETAILS the rules.
When "OPEN_REQ" is "YES"? When is it "NO"?
When "OPEN_PO" is "YES"? When is it "NO"?
What are these "COUNT" in your "expected OUTPUT"? Explain them from the data you gave.

Note the discrepancies between: "REQ_ID int," and "INSERT INTO REQ_TBL VALUES ('REQ1245',", and same thing for table "PO_TBL".
Please test your test case before posting it.

Report message to a moderator

Re: two different counts in select [message #684554 is a reply to message #684552]

Tue, 29 June 2021 00:29

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: two different counts in select [message #684555 is a reply to message #684552]

Tue, 29 June 2021 00:57

Sekhar6617
Messages: 22
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

Report message to a moderator

Re: two different counts in select [message #684556 is a reply to message #684555]

Tue, 29 June 2021 01:48

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Have a look at the query in my last post.
You have to specify what happens when there are no rows for some status category (YES/NO) in one or both tables.

Report message to a moderator

Re: two different counts in select [message #684558 is a reply to message #684556]

Tue, 29 June 2021 03:17

Sekhar6617
Messages: 22
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

Report message to a moderator

Re: two different counts in select [message #684559 is a reply to message #684558]

Tue, 29 June 2021 04:26

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What should then be the output?
If you want all combinations for YES/NO for REP and PO it is mandatory to have "duplicates". If you don't want "duplicates" then you need to specify where (in which combination of OPEN_REQ/OPEN_PO) the rows should be counted for all possible cases (there are YES/YES and NO/NO, there are YES/YES and YES/NO without NO/NO, there are NO/NO and no YES/YES without NO/YES, there are no YES/YES no NO/NO just YES/NO or NO/YES...).

Report message to a moderator

Re: two different counts in select [message #684560 is a reply to message #684559]

Tue, 29 June 2021 05:12

Sekhar6617
Messages: 22
Registered: March 2021

Junior Member

Hi Michel,

I am completely agree with you about the possibilities. But we are expecting output should be as below.

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

Thank you.

Regards
Sekhar

Report message to a moderator

Re: two different counts in select [message #684561 is a reply to message #684560]

Tue, 29 June 2021 05:30

Michel Cadot
Messages: 68641
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

Report message to a moderator

Re: two different counts in select [message #684562 is a reply to message #684561]

Tue, 29 June 2021 06:33

Sekhar6617
Messages: 22
Registered: March 2021

Junior Member

Thank you so much Michel.
Provided query is working perfectly.

Regards
Sekhar

Report message to a moderator

Previous Topic:	Listing table names,noofrows,noofcolumns primarkeyscol in a schema
Next Topic:	Getting PLS: 00306 wrong number or types of arguments in call to

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 13:20:03 CDT 2024