CODEWITHSUNDEEP
sqlpostgresqlgroup-bycountdistinct
Jappa
Jappa

Reputation: 101

count rows respecting conditions

does anyone knows how should i do to count the rows of a table where time and number are the same for different id. For the case below, i want to have (0023: 2, 0024: 3).

example:

      id_, time, number

row1: id1, 12h30, 0023
row2: id2, 12h30, 0023
row3: id2, 12h30, 0023
row4: id3, 12h45, 0024
row5: id5, 12h45, 0024
row6: id6, 12h45, 0024

Upvotes: 1

Views: 74

Answers (2)

Timur Shtatland
Timur Shtatland

Reputation: 12347

Using CTEs, you can do:

with t as (
    select * from table_name group by id, time, number
)
select count(*) as cnt from t group by time, number;

Upvotes: 0

GMB
GMB

Reputation: 222412

Do you want count(distinct ...)?

select number, time, count(distinct id) cnt
from mytable
group by number, time

Upvotes: 3

Related Questions