Sql Query with Count and Case Statement

using sql count in a case statement

SELECT 
    COUNT(CASE WHEN rsp_ind = 0 then 1 ELSE NULL END) as "New",
    COUNT(CASE WHEN rsp_ind = 1 then 1 ELSE NULL END) as "Accepted"
from tb_a

You can see the output for this request HERE

SQL Case statement with Count?

Use aggregation:

select p.person_nbr,
       (case when min(ml1.mstr_list_item_desc) = max(ml1.mstr_list_item_desc)
             then min(ml1.mstr_list_item_desc)
             else 'Unknown'
        end) as final_ethnicity
from person_table p left join
     person_ethnicity_xref eth1
     on p.person_id = eth1.person_id left join
     mstr_lists ml1
     on eth1.ethnicity_item_id = ml1.mstr_list_item_id
group by p.person_nbr;

Note: This slightly tweaks your logic. If there a multiple ethnicities and they are all the same, then that value is used.

Conditional COUNT within CASE statement

You were nearly there! I've made a few changes:

SELECT 
  DATEFROMPARTS(YEAR(t1.Date), MONTH(t1.Date),1) AS Draft_Date, 
  Membership,
  COUNT(CASE WHEN t1.Membership = 5 AND t1.Service_Type = 'A' THEN 1 END) as m5stA,
  COUNT(CASE WHEN t1.Membership = 2 AND t1.Service_Type IN ('J','C') THEN 1 END) as m2stJC
FROM Table1 t1 
GROUP BY YEAR(t1.Date), MONTH(t1.Date), Membership

Changes:

• Avoid using apostrophes to alias column names, use ascii standard " double quotes if you must
• When doing a conditional count, put the count outside the CASE WHEN, and have the case when return something (any non null thing will be fine - i used 1, but it could also have been 'x' etc) when the condition is met. Don't put an ELSE - CASE WHEN will return null if there is no ELSE and the condition is not met, and nulls don't COUNT (you could also write ELSE NULL, though it's redundant)
• Qualify all your column names, always - this helps keep the query working when more tables are added in future, or even if new columns with the same names are added to existing tables
• You forgot a THEN in the second WHEN
• You don't necessarily need to GROUP BY the output of DATEFROMPARTS. When a deterministic function is used (always produces the same output from the same inputs) the db is smart enough to know that grouping on the inputs is also fine
• Your example data didn't contain any data that would make the COUNT count 1+ by the way, but I'm sure you will have other conditional counts that work out (it just made it harder to test)

How best to Count(*) with a CASE STATEMENT?

I tend to like sum()

SELECT
SUM(CASE WHEN <conditions> THEN 1 ELSE 0 END) as conditionalcountall
FROM TABLE

Using case with count as one of conditions in SQL query

Follow postgres' hints, you must use a group by:

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE business (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    name text NOT NULL,
    brand text
);

INSERT INTO business (name, brand) VALUES
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('Auchan' , NULL),
('EpicHd' , NULL),
('Kafkflef' , 'Kafk');

SELECT
    uuid_generate_v4 () AS uuid,
    name AS name,
    CASE WHEN brand IS NOT NULL THEN
        brand
    WHEN brand IS NULL
        AND count(name) > 5 THEN
        name
    ELSE
        'Other'
    END AS brand,
    CURRENT_DATE AS time_added
FROM
    business
GROUP BY name, brand;

ROLLBACK;

Result:

f6a66b5d-fd43-4deb-be14-f052ddc142b2    Auchan  Auchan  2021-07-30
fbfd60ee-5c28-407f-8c46-21716a2e98a7    EpicHd  Other   2021-07-30
275a6d4c-6859-4a6c-a917-b818cf142809    Kafkflef    Kafk    2021-07-30

Group By with Case Statement Doesn't Count Zeros

You can use a LEFT JOIN between the ranges and your existing CTE. For example:

with myranges (indrange) as (
  select 'Less than -30%'         from sysibm.sysdummy1
  union all select '-30% to -20%' from sysibm.sysdummy1
  union all select '-20% to -10%' from sysibm.sysdummy1
  union all select '-10% to 0%'   from sysibm.sysdummy1
  union all select '0% to 10%'    from sysibm.sysdummy1
  union all select '10% to 20%'   from sysibm.sysdummy1
  union all select '20% to 30%'   from sysibm.sysdummy1
  union all select 'At Least 30%' from sysibm.sysdummy1
),
myfilter as (
  -- here add you existing CTE
)
select r.indrange, count(f.indrange) AS total
from myranges r
left join myfilter f on f.indrange = r.indrange
group by r.indrange

CASE statement subquery row count to be used as the result

You could use a subquery:

select result_column1, result_column2, result_column1 + result_column2 sum_of_results
from (
    select
        (select count(*) from subtable  where subtable.column1value  = 100) as result_column1,
        (select count(*) from subtable2 where subtable2.column2value = 100) as result_column2
) x

Note that I removed the single quotes around the literal values in the where clause; if these columns are numeric, then they should be compared as such.

---

Related Topics