Postgresql Where Count Condition

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.

PostgreSQL Where count condition

SELECT a.license_id, a.limit_call
     , count(b.license_id) AS overall_count
FROM   "License"  a
LEFT   JOIN "Log" b USING (license_id)
WHERE  a.license_id = 7 
GROUP  BY a.license_id  -- , a.limit_call  -- add in old versions
HAVING a.limit_call > count(b.license_id)

Since Postgres 9.1 the primary key covers all columns of a table in the GROUP BY clause. In older versions you'd have to add a.limit_call to the GROUP BY list. The release notes for 9.1:

Allow non-GROUP BY columns in the query target list when the primary
key is specified in the GROUP BY clause

Further reading:

• Why can't I exclude dependent columns from `GROUP BY` when I aggregate by a key?

The condition you had in the WHERE clause has to move to the HAVING clause since it refers to the result of an aggregate function (after WHERE has been applied). And you cannot refer to output columns (column aliases) in the HAVING clause, where you can only reference input columns. So you have to repeat the expression. The manual:

An output column's name can be used to refer to the column's value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING
clauses; there you must write out the expression instead.

I reversed the order of tables in the FROM clause and cleaned up the syntax a bit to make it less confusing. USING is just a notational convenience here.

I used LEFT JOIN instead of JOIN, so you do not exclude licenses without any logs at all.

Only non-null values are counted by count(). Since you want to count related entries in table "Log" it is safer and slightly cheaper to use count(b.license_id). This column is used in the join, so we don't have to bother whether the column can be null or not.

count(*) is even shorter and slightly faster, yet. If you don't mind to get a count of 1 for 0 rows in the left table, use that.

Aside: I would advise not to use mixed case identifiers in Postgres if possible. Very error prone.

How do I count occurrences with conditions in PostgreSQL?

You can use DISTINCT inside COUNT() to count the distinct times if log_type is the 'Rejection X':

SELECT name,
       COUNT(CASE WHEN log_type = 'Approval' THEN 1 END) approvals,
       COUNT(DISTINCT CASE WHEN log_type IN ('Rejection 1', 'Rejection 2') THEN time END) rejections
FROM person
GROUP BY name;

See the demo.

PostgreSQL: count rows where condition on start date and end date fits in generated time series

You can LEFT JOIN the table to the series of timestamps on the start being less then or equal to the timestamp and the end being greater than the timestamp or being NULL. Then GROUP BY the timestamps and take the count().

SELECT gs.ts,
       count(t1.started)
       FROM generate_series('2020-01-03'::timestamp, '2020-01-08'::timestamp, '1 day'::interval) gs (ts)
            LEFT JOIN tab_1 t1
                      ON t1.started <= gs.ts
                         AND (t1.ended IS NULL
                               OR t1.ended > gs.ts)
       GROUP BY gs.ts
       ORDER BY gs.ts;

db<>fiddle

Count PostgreSQL with condition

You can simplify the condition by just comparing months. Add not exists to check whether the person had a visit before the current month.

select * 
from visits
left join people on visits.id = people.id
where date_trunc('month', current_date) = date_trunc('month', visits.visit_date)
and not exists (
    select from visits
    where id = people.id
    and date_trunc('month', current_date) > date_trunc('month', visits.visit_date)
    )

SQL / Postgresql count multiple columns with conditions

You can unpivot and aggregate. One method is:

select name,
       sum(case when feature and gender = 'male' then 1 else 0 end) as num_male,
       sum(case when feature and gender = 'female' then 1 else 0 end) as num_female
from ((select 'a_feature' as name, a_feature as feature, gender
       from t
      ) union all
      (select 'b_feature' as name, b_feature, gender
       from t
      ) union all
      . . .
     ) f
group by name;

In Postgres, you would unpivot using a lateral join:

select name,
       sum(case when feature and gender = 'male' then 1 else 0 end) as num_male,
       sum(case when feature and gender = 'female' then 1 else 0 end) as num_female
from t cross join lateral
     (values ('a_feature', a_feature),
             ('b_feature', b_feature),
             . . .
     ) v(name, feature)
group by name;

You can generate the list for values() using information_schema.columns if you are reluctant to type it all in.

EDIT:

You can construct the values clause using something like this:

select string_agg('(''' || column_name || ''', column_name)', ', ') 
from information_schema.columns
where table_name = ?

Conditional SQL count

In Postgres 9.4 or later, use the aggregate FILTER option. Typically cleanest and fastest:

SELECT category
     , count(*) FILTER (WHERE question1 = 0) AS zero
     , count(*) FILTER (WHERE question1 = 1) AS one
     , count(*) FILTER (WHERE question1 = 2) AS two
FROM   reviews
GROUP  BY 1;

Details for the FILTER clause:

• Aggregate columns with additional (distinct) filters

If you want it short:

SELECT category
     , count(question1 = 0 OR NULL) AS zero
     , count(question1 = 1 OR NULL) AS one
     , count(question1 = 2 OR NULL) AS two
FROM   reviews
GROUP  BY 1;

More syntax variants:

• For absolute performance, is SUM faster or COUNT?

Proper crosstab query

crosstab() yields the best performance and is shorter for long lists of options:

SELECT * FROM crosstab(
     'SELECT category, question1, count(*) AS ct
      FROM   reviews
      GROUP  BY 1, 2
      ORDER  BY 1, 2'
   , 'VALUES (0), (1), (2)'
   ) AS ct (category text, zero int, one int, two int);

Detailed explanation:

• PostgreSQL Crosstab Query

How to count if conditional with a group by in Postgresql

you should use the CASE clause within the count function.
e.g. COUNT(CASE WHEN score > 7 THEN 1 ELSE NULL END)
(or SUM with 1 and 0 - same result...)

Regards,
Jony

---

Related Topics