CODEWITHSUNDEEP
sqlpostgresqlgroup-bycounthaving-clause
Victor Hanna
Victor Hanna

Reputation: 57

how to select the unique id count for repeated id that have a specific value

for example if i have this table

 report_date     id    customerCount    orderNr
  2020-02-20    123        12              10
  2020-02-19    123        18              11
  2020-02-18    123        0               12
  2020-02-20    321        0               0
  2020-02-19    321        0               0
  2020-02-18    321        0               0
  2020-02-20    456        17              13
  2020-02-19    456        0               0
  2020-02-18    456        15              14
  2020-02-20    654        0               0
  2020-02-19    654        0               0
  2020-02-18    654        0               0

i want to select the count of id that all of its rows are customerCount = 0 and orderNr = 0

Upvotes: 1

Views: 221

Answers (3)

vuvo
vuvo

Reputation: 61

select count(table.id)
from table 
where customerCount = 0 and orderNr = 0
group by table.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

One method uses two levels of aggregation:

select count(*)
from (select id
      from t
      group by id
      having max(customerCount) = 0 and max(orderNr) = 0
     ) i;

Note: This assumes that the values are never negative, which seems quite reasonable given the example values and naming.

Another method uses not exists:

select count(distinct id)
from t
where not exists (select 1
                  from t t2
                  where t2.id = t.id and
                        (t2.customerCount <> 0 or t.orderNr <> 0)
                 );

Upvotes: 1

GMB
GMB

Reputation: 222582

To list all the ids, you can use aggregation and having. Boolean aggregation comes handy to express the constraints:

select id
from mytable
group by id
having bool_and(customerCount = 0) and bool_and(order_nr = 0)

If you want to count how many ids satisfy the conditions, you can add another level of aggregation:

select count(*) cnt
from (
    select id
    from mytable
    group by id
    having bool_and(customerCount = 0) and bool_and(order_nr = 0)
) t

Upvotes: 1

Related Questions