Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL Query – How to combine 2 count statements in to single query

I have 2 queries which give count of cases based upon a condition

Query 1:

Select count(unique c.caseid) as New_cases from caseinfo c where 
(c.crtdate between TO_DATE('2024/07/08') AND TO_DATE('2024/07/11')) and c.channel is not null;

Query 2:

Select count(unique c.caseid) as Old_cases from caseinfo c where 
(c.crtdate between TO_DATE('2024/07/08') AND TO_DATE('2024/07/11')) and c.channel is null;

Both the queries have just a difference of null condition at the end.How can we combine these 2 in to a single query.

Any help would be appreciated.

Tags:

2

Answers


  1. 0

    Not pretty, but pragmatic.

    SELECT 'q1', count(unique c.caseid) as New_cases 
  from caseinfo c 
  where (c.crtdate between TO_DATE('2024/07/08') 
    AND TO_DATE('2024/07/11')) 
    AND c.channel is not null
UNION 
SELECT 'q2', count(unique c.caseid) as Old_cases 
  from caseinfo c 
 where (c.crtdate between TO_DATE('2024/07/08') AND TO_DATE('2024/07/11')) 
   and c.channel is null
    Login or Signup to reply.
  2. 0

    If you are trying to get distinct values you could use a case expression

    SELECT COUNT(DISTINCT( CASE WHEN c.channel is not null THEN 1 END) as New_cases,
       COUNT(DISTINCT( CASE WHEN c.channel is null THEN 1 END ) as Old_cases 
FROM caseinfo c 
WHERE c.crtdate between TO_DATE('2024/07/08') AND TO_DATE('2024/07/11');

    Otherwise you could use SUM

    SELECT SUM(c.channel is not null) as New_cases,
       SUM(c.channel is null) as Old_cases 
FROM caseinfo c 
WHERE c.crtdate between TO_DATE('2024/07/08') AND TO_DATE('2024/07/11');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search