How to include SUM not as column but as separate line in the result set-CodePudding

The query is as follows:

  SELECT  tc.CATEGORY_NAME,
  COUNT(dc.CONSULT_ID) AS "TEST_COUNT"
  FROM DOCTOR_CONSULT dc 
  INNER JOIN CONSULT_LABTEST cl 
  on(dc.CONSULT_ID=cl.CONSULT_ID)
  INNER JOIN TEST_SETUP ts
  on(cl.LABTEST_ID=ts.TEST_ID)
  INNER JOIN TEST_CATEGORY tc
  ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
 INNER JOIN OFFICE_DETAILS od 
 on(dc.OFFICE_ID=od.OFFICE_ID)  
 WHERE dc.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
 AND ts.ACTIVE_STATUS ='Y'
 AND tc.ACTIVE_STATUS ='Y'
 AND od.ACTIVE_STATUS ='Y'
 AND ts.LAB_TYPE IN('L')
 GROUP BY tc.CATEGORY_NAME;

The result is as follows:

CATEGORY_NAME   TEST_COUNT
Biochemistry        7
Hematology          6
Hormones            1
Clinical Pathology  1

What i require

CATEGORY_NAME   TEST_COUNT
Biochemistry        7
Hematology          6
Hormones            1
Clinical Pathology  1
SUM                 15

what is the modification required for the above SQL code

CodePudding user response：

union

and sum as SUM previous select

CodePudding user response：

You can add a UNION without the group to get the total count you want:

SELECT  
  tc.CATEGORY_NAME,
  COUNT(dc.CONSULT_ID) AS "TEST_COUNT"
FROM DOCTOR_CONSULT dc 
INNER JOIN CONSULT_LABTEST cl 
on(dc.CONSULT_ID=cl.CONSULT_ID)
INNER JOIN TEST_SETUP ts
on(cl.LABTEST_ID=ts.TEST_ID)
INNER JOIN TEST_CATEGORY tc
ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
INNER JOIN OFFICE_DETAILS od 
on(dc.OFFICE_ID=od.OFFICE_ID)  
WHERE dc.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
  AND ts.ACTIVE_STATUS ='Y'
  AND tc.ACTIVE_STATUS ='Y'
  AND od.ACTIVE_STATUS ='Y'
  AND ts.LAB_TYPE IN('L')
GROUP BY tc.CATEGORY_NAME

UNION

SELECT 
  'SUM' AS CATEGORY_NAME, 
  COUNT(*) AS TEST_COUNT
FROM DOCTOR_CONSULT dc 
INNER JOIN CONSULT_LABTEST cl 
on(dc.CONSULT_ID=cl.CONSULT_ID)
INNER JOIN TEST_SETUP ts
on(cl.LABTEST_ID=ts.TEST_ID)
INNER JOIN TEST_CATEGORY tc
ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
INNER JOIN OFFICE_DETAILS od 
on(dc.OFFICE_ID=od.OFFICE_ID)  
WHERE dc.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
  AND ts.ACTIVE_STATUS ='Y'
  AND tc.ACTIVE_STATUS ='Y'
  AND od.ACTIVE_STATUS ='Y'
  AND ts.LAB_TYPE IN('L')

CodePudding user response：

To include the sum of all the test counts at the bottom of the result set, you can use the UNION operator to combine the original query with a subquery that retrieves the sum of the test counts.

SELECT  tc.CATEGORY_NAME,
    COUNT(dc.CONSULT_ID) AS "TEST_COUNT"
    FROM DOCTOR_CONSULT dc 
    INNER JOIN CONSULT_LABTEST cl 
    on(dc.CONSULT_ID=cl.CONSULT_ID)
    INNER JOIN TEST_SETUP ts
    on(cl.LABTEST_ID=ts.TEST_ID)
    INNER JOIN TEST_CATEGORY tc
    ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
    INNER JOIN OFFICE_DETAILS od 
    on(dc.OFFICE_ID=od.OFFICE_ID)  
    WHERE dc.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
    AND ts.ACTIVE_STATUS ='Y'
    AND tc.ACTIVE_STATUS ='Y'
    AND od.ACTIVE_STATUS ='Y'
    AND ts.LAB_TYPE IN('L')
    GROUP BY tc.CATEGORY_NAME
    UNION
    SELECT 'SUM' AS CATEGORY_NAME, SUM(TEST_COUNT) as TEST_COUNT
    FROM (
        SELECT COUNT(dc.CONSULT_ID) as TEST_COUNT
        FROM DOCTOR_CONSULT dc 
        INNER JOIN CONSULT_LABTEST cl 
        on(dc.CONSULT_ID=cl.CONSULT_ID)
        INNER JOIN TEST_SETUP ts
        on(cl.LABTEST_ID=ts.TEST_ID)
        INNER JOIN TEST_CATEGORY tc
        ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
        INNER JOIN OFFICE_DETAILS od 
        on(dc.OFFICE_ID=od.OFFICE_ID)  
        WHERE dc.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
        AND ts.ACTIVE_STATUS ='Y'
        AND tc.ACTIVE_STATUS ='Y'
        AND od.ACTIVE_STATUS ='Y'
        AND ts.LAB_TYPE IN('L')
       GROUP BY tc.CATEGORY_NAME)