Imagine a table like this...
TRANS_TBL
REGION_NUM TYPE
---------- ----
800 D
800 D
800 P
801 P
801 D
802 P
What I'm trying to do is create a query that counts the amount of types 'D' and 'P' separately for each REGION_NUM in the TRANS_TBL. In other words, I'm trying to get these results from the example table above...
REGION_NUM DL PR
---------- -- --
800 2 1
801 1 1
802 0 1
... where DL is the number of type 'D' for that region and PR is the number of type 'P' for that region. Here's the query I have so far:
select distinct REGION_NUM, DL=(SELECT count(*) from TRANS_TBL where TYPE = 'D'),
PR=(SELECT count(*) from TRANS_TBL where TYPE = 'P') from TRANS_TBL
This query gets me one grand total of all the regions instead of separate totals for each individual region. Would anybody know to accomplish this task with one query, if it's at all possible? Am I on the right path? Any bit of help would be greatly appreciated.
TRANS_TBL
REGION_NUM TYPE
---------- ----
800 D
800 D
800 P
801 P
801 D
802 P
What I'm trying to do is create a query that counts the amount of types 'D' and 'P' separately for each REGION_NUM in the TRANS_TBL. In other words, I'm trying to get these results from the example table above...
REGION_NUM DL PR
---------- -- --
800 2 1
801 1 1
802 0 1
... where DL is the number of type 'D' for that region and PR is the number of type 'P' for that region. Here's the query I have so far:
select distinct REGION_NUM, DL=(SELECT count(*) from TRANS_TBL where TYPE = 'D'),
PR=(SELECT count(*) from TRANS_TBL where TYPE = 'P') from TRANS_TBL
This query gets me one grand total of all the regions instead of separate totals for each individual region. Would anybody know to accomplish this task with one query, if it's at all possible? Am I on the right path? Any bit of help would be greatly appreciated.