using sql count in a case statement

Depending on you flavor of SQL, you can also imply the else statement in your aggregate counts.

For example, here's a simple table Grades:

We can test out each Aggregate counter syntax like this (Interactive Demo in SQL Fiddle):

SELECT
    COUNT(CASE WHEN Letter = 'A' THEN 1 END)           AS [Count - End],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END) AS [Count - Else Null],
    COUNT(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)    AS [Count - Else Zero],
    SUM(CASE WHEN Letter = 'A' THEN 1 END)             AS [Sum - End],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE NULL END)   AS [Sum - Else Null],
    SUM(CASE WHEN Letter = 'A' THEN 1 ELSE 0 END)      AS [Sum - Else Zero]
FROM Grades

And here are the results (unpivoted for readability):

Which lines up with the docs for Aggregate Functions in SQL

Docs for COUNT:

COUNT(*) - returns the number of items in a group. This includes NULL values and duplicates.
COUNT(ALL expression) - evaluates expression for each row in a group, and returns the number of nonnull values.
COUNT(DISTINCT expression) - evaluates expression for each row in a group, and returns the number of unique, nonnull values.

Docs for SUM:

ALL - Applies the aggregate function to all values. ALL is the default.
DISTINCT - Specifies that SUM return the sum of unique values.

Close... try:

select 
   Sum(case when rsp_ind = 0 then 1 Else 0 End) as 'New',
   Sum(case when rsp_ind = 1 then 1 else 0 end) as 'Accepted'
from tb_a

The reason you're getting two rows instead of one is that you are grouping by rsp_ind in the outer query (which you did not, to my disappointment, share with us). There is nothing you can do to force one row instead of two without dealing with that GROUP BY item.

CREATE TABLE #CountMe (Col1 char(1));

INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');
INSERT INTO #CountMe VALUES ('A');
INSERT INTO #CountMe VALUES ('B');

SELECT
    COUNT(CASE WHEN Col1 = 'A' THEN 1 END) AS CountWithoutElse,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE NULL END) AS CountWithElseNull,
    COUNT(CASE WHEN Col1 = 'A' THEN 1 ELSE 0 END) AS CountWithElseZero
FROM #CountMe;

If you want to group the results based on a column and take the count based on the same, you can run the query as :

$sql = "SELECT COLUMNNAME,

COUNT(CASE WHEN COLUMNNAME IN ('YOURCONDITION') then 1 ELSE NULL END) as 'New',

COUNT(CASE WHEN COLUMNNAME IN ('YOURCONDITION') then 1 ELSE NULL END) as 'ACCPTED'

FROM TABLENAME

GROUP BY COLUMNANME";

select sum(rsp_ind = 0) as `New`,
       sum(rsp_ind = 1) as `Accepted` 
from tb_a

ok. I solved it

SELECT `smart_projects`.project_id, `smart_projects`.business_id, `smart_projects`.title,
 `page_pages`.`funnel_id` as `funnel_id`, count(distinct(page_pages.page_id) )as page_count, count(distinct (CASE WHEN page_pages.funnel_id != 0 then  page_pages.funnel_id ELSE NULL END ) ) as funnel_count
FROM `smart_projects`
LEFT JOIN `page_pages` ON `smart_projects`.`project_id` = `page_pages`.`project_id`
WHERE  smart_projects.status !=  0 
AND `smart_projects`.`business_id` = 'cd9412774edb11e9'
GROUP BY `smart_projects`.`project_id`
ORDER BY `title` DESC

Added this solution, as I came here while searching for how to classify and count data in one statement. Using a double select you can get a query that has the count and the 'classification' in the result. Hope someone finds this useful.

SELECT A, COUNT(A) AS Count FROM (
        SELECT 
            CASE
                WHEN [Col1] = 'Option1' OR [Col1] = 'Option2'
                THEN 'Classification 1'
                 
                WHEN [Col1] = 'Option3' OR 'Option4' 
                THEN 'Classification 2'
    
                WHEN [Col1] = 'Option4' 
                THEN 'Classification 3'
            END As A
          FROM [Table1] 
          ) AS G
    GROUP BY A

Simple:

SELECT
    COUNT(CASE WHEN user_type = 'Admin' THEN 1 ELSE NULL END) AS mid_dead_count
FROM
    users
WHERE
    DATE = '2022-01-02';