Search code examples
sqloracle12c

How to show counts in separate columns in SQL


I am just starting out using SQL and trying to figure everything out. Here is the start to my code:

SELECT 
Count(DL_Service_ID) AS Group,
Count(DL_Service_ID) AS Individual,
COMPLETED_DATE
FROM DAILY_LOG_DATA 
WHERE Group = '8369' AND Individual = '8219'
AND BETWEEN '2020-04-05' AND '2020-04-11';

What I am trying to accomplish is counting the number of services(8369 and 8219) from the column DL_service_ID from the dates 2020-04-05 and 2020-04-11(COMPLETED_DATE). I would like to show the results in separate columns (Individual) which is 8219 and (Group) which is 8369. The dates are shown like this: 2020-04-07 11:14:19 in the COMPLETED_DATE column, not sure if that changes anything. I am also using DBeaver.


Solution

  • FROM DAILY_LOG_DATA WHERE DL_Service_ID = '8369'
    AND COMPLETED_DATE BETWEEN '2020-04-05' AND '2020-04-11'
    GROUP BY DL_Service_ID,COMPLETED_DATE
    UNION
    SELECT DL_Service_ID AS Individual, Count(DL_Service_ID) AS "Group", COMPLETED_DATE 
    FROM DAILY_LOG_DATA WHERE DL_Service_ID = '8219'
    AND COMPLETED_DATE BETWEEN '2020-04-05' AND '2020-04-11'
    GROUP BY DL_Service_ID,COMPLETED_DATE
    

    enter image description here