Home » SQL & PL/SQL » SQL & PL/SQL » Show multiple count in one result set (Oracle 12c)
Show multiple count in one result set [message #666024] Mon, 09 October 2017 10:52 Go to next message
fabi88
Messages: 80
Registered: November 2011
Member
Hi,
There are two table:

Table patient:
P_id Name BirthDate
1 N1. 2016-08-02
2 N2. 2015-05-02
3 N3. 2013-06-01
4. N4. 2014-01-09

Table visited:(p_id is foreign key to table patient)
Id. Role_id. P_id. Visit_date
1. 10. 1 2017-03-05
2. 11. 2. 2017-01-01
3. 10. 2. 2017-02-03
4. 12. 3. 2016-05-07
5. 11. 4. 2016-04-09
6. 10. 1. 2017-04-09

We are going to get the count of visited patient who their old are under 1, between 1 and 2, between 2 and 3 at date of visit_date by each role_id.

The results like :
Role_id. Under_one. Bet_one_two. Bet_two- three
10. 2. 1. 0
11. 0. 1. 1
12. 0. 0. 1
Could anyone help me how write a query for getting the results?
Thank you in advance.



Re: Show multiple count in one result set [message #666026 is a reply to message #666024] Mon, 09 October 2017 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25741
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Show multiple count in one result set [message #666032 is a reply to message #666026] Mon, 09 October 2017 11:17 Go to previous messageGo to next message
fabi88
Messages: 80
Registered: November 2011
Member
Sorry.
There are two table:

Table patient:
P_id        Name        BirthDate
1             N1.         2016-08-02
2             N2.         2015-05-02
3             N3.         2013-06-01
4.            N4.         2014-01-09

Table visited:(p_id is foreign key to table patient)
Id.   Role_id.  P_id.         Visit_date
1.        10.        1             2017-03-05
2.         11.       2.            2017-01-01
3.         10.       2.            2017-02-03
4.         12.       3.             2016-05-07
5.          11.      4.            2016-04-09
6.          10.      1.             2017-04-09

We are going to get the count of visited patient who their old are under 1, between 1 and 2, between 2 and 3 at date of visit_date by each role_id.

The results like :

Role_id. Under_one. one_two. two- three
10. 2. 1. 0
11. 0. 1. 1
12. 0 0. 1



Could anyone help me how write a query for getting the results?
Thank you in advance.
Re: Show multiple count in one result set [message #666033 is a reply to message #666032] Mon, 09 October 2017 11:35 Go to previous messageGo to next message
John Watson
Messages: 7176
Registered: January 2010
Location: Global Village
Senior Member
Take it slowly.
Begin by writing the two CREATE TABLE statements and the ten INSERT statements. Post them here.
Then go on to the SELECT. Start by writing a query that will JOIN the two tables.
Re: Show multiple count in one result set [message #666041 is a reply to message #666033] Tue, 10 October 2017 03:14 Go to previous messageGo to next message
quirks
Messages: 60
Registered: October 2014
Member
How about that?
WITH
    PATIENT(P_ID, NAME, BIRTHDATE)
    AS
        (SELECT 1, 'N1.', TO_DATE('2016-08-02', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 2, 'N2.', TO_DATE('2015-05-02', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 3, 'N3.', TO_DATE('2013-06-01', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 4, 'N4.', TO_DATE('2014-01-09', 'YYYY-MM-DD') FROM DUAL),
    VISITED(ID
           ,ROLE_ID
           ,P_ID
           ,VISIT_DATE)
    AS
        (SELECT 1, 10, 1, TO_DATE('2017-03-05', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 2, 11, 2, TO_DATE('2017-01-01', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 3, 10, 2, TO_DATE('2017-02-03', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 4, 12, 3, TO_DATE('2016-05-07', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 5, 11, 4, TO_DATE('2016-04-09', 'YYYY-MM-DD') FROM DUAL
         UNION ALL
         SELECT 6, 10, 1, TO_DATE('2017-04-09', 'YYYY-MM-DD') FROM DUAL),
    CALC_AGE
    AS
        (SELECT VISITED.ROLE_ID
               ,TRUNC((TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYYMMDD')) - TO_NUMBER(TO_CHAR(BIRTHDATE, 'YYYYMMDD'))) / 10000) AGE
           FROM PATIENT, VISITED
          WHERE PATIENT.P_ID = VISITED.P_ID)
SELECT   ROLE_ID
        ,"under_one"
        ,"one_two"
        ,"two_three"
    FROM CALC_AGE PIVOT (COUNT(*) FOR AGE IN (0 AS "under_one", 1 AS "one_two", 2 AS "two_three"))
ORDER BY ROLE_ID;


   ROLE_ID  under_one    one_two  two_three
---------- ---------- ---------- ----------
        10          2          1          0
        11          0          1          1
        12          0          0          1

3 rows selected.
Re: Show multiple count in one result set [message #666048 is a reply to message #666041] Tue, 10 October 2017 07:43 Go to previous messageGo to next message
fabi88
Messages: 80
Registered: November 2011
Member
Thank you so much, Your solution resolved my problem, thank you again, I also resolved it by the following query:


WITH visitAge(visitor_ID, VisitAge) AS
(SELECT 
    v.visitor_ID, TRUNC((TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYYMMDD')) - TO_NUMBER(TO_CHAR(BIRTHDATE, 'YYYYMMDD'))) / 10000) as VisitAge
FROM visited v
     INNER JOIN persons p on v.p_ID = p.p_ID
)
SELECT
    visitor_ID, 
    SUM(CASE WHEN  VisitAge < 1 THEN 1 ELSE 0  END ) AS under_one,
    SUM(CASE WHEN  VisitAge >= 1 AND  VisitAge < 2 THEN 1 ELSE 0 END  ) AS Bet_one_two,
    SUM(CASE WHEN  VisitAge >= 2 AND  VisitAge < 3 THEN 1 ELSE 0 END  )  AS Bet_two_three
FROM
  visitAge
GROUP BY visitor_ID;  
Re: Show multiple count in one result set [message #666051 is a reply to message #666048] Tue, 10 October 2017 09:17 Go to previous message
quirks
Messages: 60
Registered: October 2014
Member
Cool

... glad it worked for you Smile
Previous Topic: Left Outer Join with SubQuery
Next Topic: row-to-row navigation query
Goto Forum:
  


Current Time: Sun Dec 10 18:23:28 CST 2017

Total time taken to generate the page: 0.03074 seconds