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 |
|
fabi88
Messages: 112 Registered: November 2011
|
Senior 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 #666041 is a reply to message #666033] |
Tue, 10 October 2017 03:14 |
|
quirks
Messages: 82 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 |
|
fabi88
Messages: 112 Registered: November 2011
|
Senior 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;
|
|
|
|
Goto Forum:
Current Time: Wed Sep 25 07:50:49 CDT 2024
|