Select a column with different status condition in SQL

Question

In my output I got two count values 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?

the first two counts i need to check the status 1 AND 2.The next count I want the total count without checking status. — robins, Jun 06 '22 at 07:54
Please provide example data for the two tables, demonstrating the structure and behaviour of your data. Then, include the results you'd expect for that example data. Please read : [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) — MatBailie, Jun 06 '22 at 07:54
Total count of distinct sc.subgroupId or distinct sc.staff or of sc.subgroupId or of sc.staff — VBoka, Jun 06 '22 at 07:55
When using MySQL, use [Window Function Concepts and Syntax](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html), or if you use MSSQL, the description is: [SELECT - OVER Clause](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver16) and check [How to get multiple counts with one SQL query](https://stackoverflow.com/a/12789493/724039) — Luuk, Jun 06 '22 at 07:57
Now I got the count of distinct staff and distinct subgroup.I want the count of total records count(sc.id) without checking status — robins, Jun 06 '22 at 07:57

score 1 · Accepted Answer · answered Jun 06 '22 at 08:19

1

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

answered Jun 06 '22 at 08:19

VBoka

8,995
3
16
24

Its working.I have one more doubt this query(Sub query) makes any loading issue.In our production db contain around 50k records. – robins Jun 06 '22 at 08:26
1

If the answer is correct I am happy and it would be nice from you to accept it like correct answer and maybe even upvote. Then I suggest to ask a new questions regarding that problem. – VBoka Jun 06 '22 at 08:30

David Alexander · Answer 2 · 2022-06-06T08:00:38.120

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.

This answer is not working perfectly – robins Jun 06 '22 at 08:12 — robins, Jun 06 '22 at 08:12

Select a column with different status condition in SQL

2 Answers2