How to get frequency of each value in two columns in a mapping table-CodePudding

We have a mapping table with two columns, SITE_FROM & SITE_TO, the data is like:

We want to run a query to summarize for each site, get count in the SITE_FROM column and count in the SITE_TO column, such as:

<- desired

The explanation is, S1 appeared 2 times in SITE_FROM, 2 times in SITE_TO. S2 appeared 3 times in SITE_FROM, 4 times in SITE_TO. S3 appears 0 times in SITE_FROM, 3 times in SITE_TO...

I came up with some query:

SELECT SITE_FROM AS SITE,
count(*) CNT,
count(decode(SITE_TO,'S1',1)) S1,
count(decode(SITE_TO,'S2',1)) S2,
count(decode(SITE_TO,'S3',1)) S3,
count(decode(SITE_TO,'S4',1)) S4,
count(decode(SITE_TO,'S5',1)) S5
FROM site_changes
GROUP BY SITE_FROM
ORDER BY 1
;

But it returns detailed site to site mapping, also it has to have hard coded values in it, if later more sites are added, we need to remember to update the query as well:

<- undesired

Thank you for your time.

CodePudding user response：

I union'd both sets to get the full permutation of to and from site id's. Then joined into that set twice with left outer joins. To avoid nulls I used NVL replacing the null with 0.

SELECT
SITE
, NVL(Z.SITE_FROM,0) SITE_FROM
, NVL(A.SITE_TO,0) SITE_TO
FROM
(
SELECT sitefrom SITE
FROM
site_changes
UNION
SELECT siteto SITE
from site_changes
)  X
LEFT OUTER JOIN 
(
    SELECT COUNT(1) SITE_FROM, SITEFROM FROM site_changes GROUP BY SITEFROM
) Z ON X.SITE = Z.SITEFROM
LEFT OUTER JOIN 
(
    SELECT COUNT(1) SITE_TO, SITETO FROM site_changes GROUP BY SITETO
)  A ON X.SITE = A.SITETO

CodePudding user response：

First get a defined list of all sites by unironing the data from site for each column. (CTE BELOW) this allows for any site to be added (no hard coding)
then join the CTE to CITE for from and to and count with group by.
since the left join will result in NULLS when no matches are found, and nulls do not get counted we should get the desired counts.

DEMO: https://dbfiddle.uk/k3ehGbIt

CTE AS (SELECT SITE_FROM as SITE FROM SITE_CHANGES
    UNION
    SELECT SITE_TO FROM SITE_CHANGES)



SELECT A.SITE, 
       coalesce(SUM(B.SITE_FROM_CNT),0) as SITE_FROM, 
       coalesce(SUM(C.SITE_TO_CNT) ,0) as SITE_TO
FROM CTE A
LEFT JOIN (SELECT SITE_FROM, count(SITE_FROM) SITE_FROM_CNT 
           FROM SITE_CHANGES 
           GROUP BY SITE_FROM) B
 on A.SITE= B.SITE_FROM
LEFT JOIN (SELECT SITE_TO, count(SITE_TO) SITE_TO_CNT 
           FROM SITE_CHANGES 
           GROUP BY SITE_TO) C
 on A.SITE = C.SITE_TO
GROUP BY A.SITE
ORDER BY A.SITE

Giving us:

 ------ ----------- --------- 
| SITE | SITE_FROM | SITE_TO |
 ------ ----------- --------- 
| S1   |         2 |       2 |
| S2   |         3 |       4 |
| S3   |         0 |       3 |
| S4   |         4 |       1 |
| S5   |         1 |       0 |
 ------ ----------- ---------