Oracle query to get counts from multiple tables and display them side by side

Question

I am trying to write a query to get count of devices from 11 different table, ie there will be 11 separate counts from 11 tables. I need to join them based on a key column and display them side by side based on the key column. Device is the key column. So the output should be Device A followed by the 11 different counts from 11 different tables.

Device A Count 1 count 2 count 3 .....
Device B Count 1 Count 2 Count 3....
Device C Count 1 Count 2 Count 3...

So here is what i have done so far. I created a view doing a union all of all the 11 tables. And used that as the source table to derive my counts.I have written sub queries in the select clause. It works but the problem is its extremely slow, takes forever since the data volume is huge. Am sure this is not the best approach but am running out of ideas... Would like to know if there is a efficient and simpler approach to solve this problem.

 select
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'STAGE_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) stg_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'PASSED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_pass_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'TEST_CTDI' and test_result = 'FAILED' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) test_fail_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_total_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT in ('FS001','FS005')
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_wlbl_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REFURB_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) refurb_ber_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_sent_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS002'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repaired_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS001'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ntf_count,
( SELECT COUNT(DISTINCT(serial_nbr)) FROM V_CPE_BAU_METRICS_SRC WHERE file_type = 'REPAIR_CTDI' and TEST_RESULT = 'FS003'
and model_id = b.model_id
and nvl(REC_CREATE_DT_KEY,2) = nvl(b.REC_CREATE_DT_KEY,2) and nvl(device_id,1) = nvl(b.device_id,1)) repair_ber_count
from V_CPE_BAU_METRICS_SRC b
group by
B.MODEL_ID,
B.REC_CREATE_DT_KEY,
B.DEVICE_ID;

Answer 1

You can do it with SELF JOIN.

I have given the demo of the first two counts as following, You can implement the code for other counts in the same way.

SELECT
    MODEL_ID,
    REC_CREATE_DT_KEY,
    DEVICE_ID,
    COUNT(DISTINCT STG_PASS_COUNT) AS STG_PASS_COUNT, -- COUNT OF DISTINCT SERIAL NUMBER 
    COUNT(DISTINCT STG_FAIL_COUNT) AS STG_FAIL_COUNT  -- COUNT OF DISTINCT SERIAL NUMBER 
    --
    -- ..........
    --
    --
FROM
    ( --OUTER SELECT IS NEEDED FOR TAKING DISTINCT VALUES IN THE COUNT
        SELECT
            B.MODEL_ID,
            B.REC_CREATE_DT_KEY,
            B.DEVICE_ID,
            CASE ---- IF CONDITION IS SATISFIED THEN ITS SERIAL NUMBER IS CONSIDERED
                WHEN B1.FILE_TYPE = 'STAGE_CTDI'
                     AND B1.TEST_RESULT = 'PASSED' THEN B1.SERIAL_NBR
            END AS STG_PASS_COUNT, 
            CASE -- IF CONDITION IS SATISFIED THEN ITS SERIAL NUMBER IS CONSIDERED
                WHEN B1.FILE_TYPE = 'STAGE_CTDI'
                     AND B1.TEST_RESULT = 'FAILED' THEN B1.SERIAL_NBR
            END AS STG_FAIL_COUNT
    --
    -- PUT ALL OTHER CONDITIONS AS ABOVE
    --
        FROM
            V_CPE_BAU_METRICS_SRC B
            JOIN V_CPE_BAU_METRICS_SRC B1 
            -- COMMON CONDITION IS USED FOR SELF JOIN
            ON ( B1.MODEL_ID = B.MODEL_ID
                AND NVL(B1.REC_CREATE_DT_KEY, 2) = NVL(B.REC_CREATE_DT_KEY, 2)
                AND NVL(B1.DEVICE_ID, 1) = NVL(B.DEVICE_ID, 1) )
    )
GROUP BY
    MODEL_ID,
    REC_CREATE_DT_KEY,
    DEVICE_ID;

Cheers!!

Answer 2

Use a Common Table Expression to eliminate the subqueries:

WITH cteCounts AS (SELECT MODEL_ID,
                          NVL(REC_CREATE_DT_KEY, 2) AS REC_CREATE_DT_KEY,
                          NVL(DEVICE_ID, 1) AS DEVICE_ID,
                          FILE_TYPE,
                          TEST_RESULT,
                          COUNT(DISTINCT(SERIAL_NBR)) AS COUNT_DISTINCT_SERIAL_NBR
                     FROM V_CPE_BAU_METRICS_SRC
                     GROUP BY MODEL_ID,
                              NVL(REC_CREATE_DT_KEY, 2),
                              NVL(DEVICE_ID, 1),
                              FILE_TYPE,
                              TEST_RESULT)
SELECT b.MODEL_ID,
       b.REC_CREATE_DT_KEY,
       b.DEVICE_ID,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'STAGE_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS STG_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'PASSED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_PASS_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'TEST_CTDI' AND c.TEST_RESULT = 'FAILED' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS TEST_FAIL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_TOTAL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT IN ('FS001','FS005') THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_WLBL_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REFURB_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REFURB_BER_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_SENT_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS002' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIRED_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS001' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_NTF_COUNT,
       SUM(CASE
             WHEN c.FILE_TYPE = 'REPAIR_CTDI' AND c.TEST_RESULT = 'FS003' THEN COUNT_DISTINCT_SERIAL_NBR
             ELSE 0
           END) AS REPAIR_BER_COUNT
  FROM V_CPE_BAU_METRICS_SRC b
  INNER JOIN cteCounts c
    ON c.MODEL_ID = b.MODEL_ID AND
       c.REC_CREATE_DT_KEY = NVL(b.REC_CREATE_DT_KEY, 2) AND
       c.DEVICE_ID = NVL(b.DEVICE_ID, 1)
  GROUP BY b.MODEL_ID,
           b.REC_CREATE_DT_KEY,
           b.DEVICE_ID