Select a column with different status condition in sql-CodePudding

In my output I got two count valus based on the condition.

SELECT count(distinct sc.staff),
       count(distinct sc.subgroupId) 
FROM schedules sc
WHERE EXISTS (SELECT *
              FROM schedulegroup sg
              WHERE sg.groupid = 15
              AND sc.subgroupId = sg.subgroup 
              AND sg.created BETWEEN '2022-06-01' AND '2022-06-12' 
              AND sc.status IN(1,2)
              )

Now I got the result based on the status condition(Status = 1 and 2). But I need a column of total count without checking the status condition.How to get the count of total records in the existing query.

Anyone please help me.Thanks in advance

CodePudding user response：

If I'm understanding you correctly, you want to select the existing data for each schedule, and also select the total (i.e. without the status filter) number of matching schedulegroup rows for each schedule.

If so, this should do it:

SELECT count(distinct sc.staff),
       count(distinct sc.subgroupId),
       count(
           SELECT *
              FROM schedulegroup sg
              WHERE sg.groupid = 15
              AND sc.subgroupId = sg.subgroup 
              AND sg.created BETWEEN '2022-06-01' AND '2022-06-12'
       )
FROM schedules sc
WHERE EXISTS (SELECT *
              FROM schedulegroup sg
              WHERE sg.groupid = 15
              AND sc.subgroupId = sg.subgroup 
              AND sg.created BETWEEN '2022-06-01' AND '2022-06-12' 
              AND sc.status IN(1,2)
              )

Or do you want to add the non-status-filtered count(distinct sc.staff) and count(distinct sc.subgroupId), rather than just the row count? If so, you could duplicate the new subquery above and modify it for each count(distinct ...) that you want to return.

You could probably make this cleaner (less repetitive) with a Common Table Expression or a user-defined function.

CodePudding user response：

Use combination of count with case when then end.

SELECT count(distinct case when sc.status in (1, 2) then sc.staff end),
       count(distinct case when sc.status in (1, 2) then sc.subgroupId end),
       count(sc.id)
FROM schedules sc
WHERE EXISTS (SELECT *
              FROM schedulegroup sg
              WHERE sg.groupid = 15
              AND sc.subgroupId = sg.subgroup 
              AND sg.created BETWEEN '2022-06-01' AND '2022-06-12')

Here is a small demo