fetch data with count and group by in postgres

Question

I have a table mail_details

|   mail_id   | sent_time          |  failed_time       | mail_type   |
|   (bigint)  |  (timestamp)       |    (timestamp)     |  (varvharing)
-----------------------------------------------------------------------
|    1        | 2020-02-05         |  null(default)     |   type-t       
|    2        | 2020-02-05         |  null(default)     |   type-t
|    3        | 2020-02-05         |  null(default)     |   type-m
|    4        | 2020-02-05         |  null(default)     |   type-p
|    5        | null(default)      |  2020-02-05        |   type-p
|    6        | 2020-02-05         |  null(default)     |   type-m
|    8        | 2020-02-05         |  null(default)     |   type-m
|    9        | null(default)      |  2020-02-05        |   type-m
|    10       | 2020-02-05         |  null(default)     |   type-n
|    11       | 2020-02-05         |  null(default)     |   type-n

Whenever the mail sent to the user I updated sent_time or mail sent failed I update failed_time

Now I want to fetch total number of mail sent (count) and the total number of mail failed (count) with the respective date where mail_type=(type_t or type_p or type_m or type_n)

output will be like

+---------------------+----------------+----------------+
|date                 |tatal_sent_mail |tatal_failed_mail|
+---------------------+----------------+----------------+
|2020-05-05           |8               |2               |
+---------------------+----------------+----------------+

I have tried with count but did not work. Any help will be appriciated.

Hi @zealous I am beginner and tried with count and group by but it did not wored — bala
– bala, Commented May 7, 2020 at 18:55
@bala - Could you try all answers and comment what works and what doesn't? You should try for a data for which an entry for a date exists sent_time and doesn't for failed_time and vice versa to have a 100% coverage on your test. — VN'sCorner
– VN'sCorner, Commented May 7, 2020 at 19:19

Thom Brown · Accepted Answer · 2020-05-07 19:03:42Z

1

You can summarise using aggregate filters, and coalesce the dates between columns.

SELECT
  coalesce(sent_time, failed_time) AS date,
  count(*) FILTER (WHERE sent_time IS NOT NULL) AS total_sent_mail,
  count(*) FILTER (WHERE failed_time IS NOT NULL) AS total_failed_mail
FROM mail_details
WHERE mail_type IN ('type_t','type_p','type_m','type_n')
GROUP BY 1
ORDER BY 1;

If you need rows for missing dates, you would need to resort to using generate_series().

answered May 7, 2020 at 19:03

Thom Brown

2,0698 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bala Over a year ago

why it gives an extra row with blank date and 0 counts @Thom Brown

Thom Brown Over a year ago

Do you have a row where both sent_mail and failed_mail are NULL? If you do, you can just filter that out in the WHERE clause.

bala Over a year ago

Ok got it. One more thing is that sent_time is a timestamp so for sent time 2020-05-08 01:10:53.847912 and 2020-05-08 01:30:53.847912 it returns two rows with count 1 and 1 but I want it to consider only date not time(h:s:i) and returns one row with count 2. @Thom Brown

Thom Brown Over a year ago

You can just cast them to dates, so use sent_time::date, failed_time::date in the coalesce parameters. No need to do that in the FILTER clause though.

Gordon Linoff · Accepted Answer · 2020-05-08 00:14:40Z

1

I would unpivot using a lateral join and aggregate:

select time::date, sum(is_sent), sum(is_fail)
from mail_details md cross join lateral
     (values (sent_time, (sent_time is not null)::int, 0)
             (failed_time, 0, (failed is not null)::int)
     ) v(time, is_sent, is_fail)
where t.time is not null and
      md.mail_type in ('type_t','type_p','type_m','type_n')
group by time::date

edited May 8, 2020 at 0:14

answered May 7, 2020 at 19:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

3 Comments

bala Over a year ago

I did not got this query i think you have missed where mail_type

bala Over a year ago

I did not got this query i think you have missed where mail_type @Gordon Linoff

Gordon Linoff Over a year ago

@bala . . . It was not clear to me if that really required filtering. You listed all the mail types in your sample data, so no filtering seemed necessary.

VN'sCorner · Accepted Answer · 2020-05-07 19:15:28Z

0

Another one using FULL OUTER JOIN on the inline view,

select sent_time as date,tatal_sent_mail,tatal_failed_mail
from 
(select sent_time,count(1) tatal_sent_mail
where mail_type in ('type_t','type_p','type_m','type_n')
group by sent_time) sent_data
FULL OUTER JOIN 
(select failed_time Final_date,count(1) tatal_failed_mail
where mail_type in ('type_t','type_p','type_m','type_n')
group by failed_time) failed_Data
ON sent_data.sent_time = failed_Data.failed_time

answered May 7, 2020 at 19:15

VN'sCorner

1,5521 gold badge9 silver badges13 bronze badges

Comments

timhealz · Accepted Answer · 2020-05-07 19:26:37Z

0

Using a union, this should do it:

SELECT  dt,
        SUM(CASE WHEN event == 'SENT' then 1 else 0 end) total_sent_mail,
        SUM(CASE WHEN event == 'FAIL' then 1 else 0 end) total_failed_mail,
FROM
    (   SELECT  'SENT' as event,
                sent_time as dt 
        FROM    mail_details
        WHERE   sent_time IS NOT NULL   )
    UNION ALL
    (   SELECT  'FAIL' as event,
                failed_time as dt
        FROM    mail_details
        WHERE   failed_time IS NOT NULL  )
GROUP BY dt

edited May 7, 2020 at 19:26

answered May 7, 2020 at 18:59

timhealz

945 bronze badges

Collectives™ on Stack Overflow

fetch data with count and group by in postgres

4 Answers 4

4 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related