sql - How to group by a, b and return set of N rows of b -

using postgres 9.3.2, want count of req_status grouped req_time , customer_id , return set of n rows each customer_id, when req_status count zero.

req_time     req_id   customer_id     req_status -----------------------------------------------  2014-03-19    100        1            'failed' 2014-03-19    102        1            'failed' 2014-03-19    105        1            'ok' 2014-03-19    106        2            'failed' 2014-03-20    107        1            'ok' 2014-03-20    108        2            'failed' 2014-03-20    109        2            'ok' 2014-03-20    110        1            'ok' 

output

req_time  customer_id   req_status  count ------------------------------------------- 2014-03-19    1            'failed'   2 2014-03-19    1            'ok'       1 2014-03-19    2            'failed'   1 2014-03-19    2            'ok'       0 2014-03-20    1            'failed'   0 2014-03-20    1            'ok'       2 2014-03-20    2            'failed'   1 2014-03-20    2            'ok'       1 

how can achieve this?

to see missing rows in result, left join complete grid of possible rows. grid built possible combinations of (req_time, customer_id, req_status) cross joins:

select d.req_time, c.customer_id, s.req_status, count(t.req_time) ct  (    select generate_series (min(req_time), max(req_time), '1 day')::date      tbl    ) d(req_time) cross  join (select distinct customer_id tbl)  c(customer_id) cross  join (values ('failed'::text), ('ok'))       s(req_status) left   join  tbl t using (req_time, customer_id, req_status) group  1,2,3 order  1,2,3; 

count on column actual table, 0 if no match found (null values don't count).

assuming req_time date (not timestamp).

similar answer here:
array_agg group , null