Conditional Count on a Field

Conditional Count on a field

I think you may be after

select 
    jobID, JobName,
    sum(case when Priority = 1 then 1 else 0 end) as priority1,
    sum(case when Priority = 2 then 1 else 0 end) as priority2,
    sum(case when Priority = 3 then 1 else 0 end) as priority3,
    sum(case when Priority = 4 then 1 else 0 end) as priority4,
    sum(case when Priority = 5 then 1 else 0 end) as priority5
from
    Jobs
group by 
    jobID, JobName

However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by,

Is it possible to specify condition in Count()?

If you can't just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values:

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sum aggregate in a similar way:

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

Aggregation Conditional Count on Present Fields

The $cond and $ifNull operators are the helpers here:

[
  {
    "$group": {
      "_id": {
        "$dayOfMonth": "$dateCreated"
      },
      "total": {
        "$sum": 1
      },
      "sent": {
        "$sum": { "$cond": [ { "$ifNull": [ "$dateSent", false ] }, 1, 0 ] }
      },
      "attempted": {
        "$sum": { "$cond": [ { "$ifNull": [ "$dateAttempted", false ] }, 1, 0 ] }
      },
      "viewed": {
        "$sum": { "$cond": [ { "$ifNull": [ "$dateViewed", false ] }, 1, 0 ] }
      },
      "clicked": {
        "$sum": { "$cond": [ { "$ifNull": [ "$dateClicked", false ] }, 1, 0 ] }
      }
    }
  }
]

$ifNull will return either the field where present ( a logical true ) or the alternate value false. And the $cond looks at this condition and returns either 1 where true or 0 where false to provide the conditional count.

Count With Conditional on PostgreSQL

Subtract from COUNT(*) the distinct number of person.ids with person.ref_signup IN ('emp', 'oth'):

 SELECT 
  COUNT(*) - 
  COUNT(DISTINCT CASE WHEN p.ref_signup IN ('emp', 'oth') THEN p.id END) as visit_count 
FROM visits v LEFT JOIN people p
ON p.id = v.id

See the demo.

Result:

| visit_count |
| ----------- |
| 4           |

Note: this code and demo fiddle use the column names of your sample data.

Pandas count column a if column b condition

Let's say your dataframe is df:

df[df['date'] == 6]['location'].value_counts()

Will give you your desired result:

paris    2
rome     1
Name: location, dtype: int64

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)

Conditional count using CASE WHEN

I think I understand your question correctly, if not let me know.

You can use a windowed function to accomplish this:

SELECT
ITEM,
COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
FROM T

This will give you the same number of rows, so you if you want the unique from this, you can use a CTE or sub-query it like so:

SELECT DISTINCT
ITEM, UNIQ_CNT_OF_CID
FROM
    (
    SELECT
    ITEM,
    COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
    FROM T
    ) final

Conditional count of rows where at least one peer qualifies

For the updated question:

SELECT ever_treated, sum(outcome_ct) AS count
FROM  (
   SELECT id
        , max(treatment) AS ever_treated
        , count(*) FILTER (WHERE outcome = 1) AS outcome_ct
   FROM   t
   GROUP  BY 1
   ) sub
GROUP  BY 1;

 ever_treated | count 
--------------+-------
            0 |     1
            1 |     3

db<>fiddle here

Read:

• For those who got no treatment at all (all treatment = 0), we see 1 x outcome = 1.
• For those who got any treatment (at least one treatment = 1), we see 3 x outcome = 1.

Would be simpler and faster with proper boolean values instead of integer.

Conditional counts in pandas group by

You can try replace your 2 lines with .count() to .sum(), as follows:

d['Zero_Balance_days'] = (x['Balance'] < 0).sum() 
d['Over_Credit_days'] = (x['Balance'] > x['Max Credit']).sum()

.count() returns number of non-NA/null observations in the Series of boolean index while both True/False are not NA/null and will be counted as well.

.sum() returns the sum of entries of True since True is interpreted as 1 while False is interpreted as 0 in the summation.

---

Related Topics