SQL column to count grouped values

Question

I'm looking to display how many of the jobs in the job table that are being grouped by customer_id are approved. Approval is determined if approval_date is not null. So if there are 12 total jobs, I want to show that 7 are approved, for example. If the approval date is null, the job is not approved.

select   
  c.customer_name
  ,count(*) as counts
  , -- i want to add a column here that tells me which jobs have (j.approval_date is not null)
from job j
join customer c on j.customer_id = c.customer_id
group by c.customer_name

The code below gives syntax error:

,count(approval_date is not null) as is_approved

Not quite -- the approval date must not be null in order for the job to be considered as approved. — Adam Levitt, Sep 25 '17 at 14:18
`COUNT( expression )` only counts values, something NULL lacks as discussed [here](https://stackoverflow.com/questions/26368794/why-arent-nulls-counted-in-countcolumnname). — HABO, Sep 25 '17 at 15:18

score 2 · Accepted Answer · answered Sep 25 '17 at 14:18

2

You could use conditional sum:

select   
  c.customer_name
  ,count(*) as counts
  ,SUM(CASE WHEN j.approval_date is not null THEN 1 ELSE 0 END)
from job j
join customer c on j.customer_id = c.customer_id
group by c.customer_name;

answered Sep 25 '17 at 14:18

Lukasz Szozda

162,964
23
234
275

SQL column to count grouped values

1 Answers1