function to calculate aggregate sum count in postgresql

Question

Is there a function that calculates the total count of the complete month like below? I am not sure if postgres. I am looking for the grand total value.

2012-08=# select date_trunc('day', time), count(distinct column) from table_name group by 1 order by 1;

     date_trunc      | count 
---------------------+-------
 2012-08-01 00:00:00 |    22
 2012-08-02 00:00:00 |    34
 2012-08-03 00:00:00 |    25
 2012-08-04 00:00:00 |    30
 2012-08-05 00:00:00 |    27
 2012-08-06 00:00:00 |    31
 2012-08-07 00:00:00 |    23
 2012-08-08 00:00:00 |    28
 2012-08-09 00:00:00 |    28
 2012-08-10 00:00:00 |    28
 2012-08-11 00:00:00 |    24
 2012-08-12 00:00:00 |    36
 2012-08-13 00:00:00 |    28
 2012-08-14 00:00:00 |    23
 2012-08-15 00:00:00 |    23
 2012-08-16 00:00:00 |    30
 2012-08-17 00:00:00 |    20
 2012-08-18 00:00:00 |    30
 2012-08-19 00:00:00 |    20
 2012-08-20 00:00:00 |    24
 2012-08-21 00:00:00 |    20
 2012-08-22 00:00:00 |    17
 2012-08-23 00:00:00 |    23
 2012-08-24 00:00:00 |    25
 2012-08-25 00:00:00 |    35
 2012-08-26 00:00:00 |    18
 2012-08-27 00:00:00 |    16
 2012-08-28 00:00:00 |    11
 2012-08-29 00:00:00 |    22
 2012-08-30 00:00:00 |    26
 2012-08-31 00:00:00 |    17
(31 rows)
--------------------------------
      Total          |    12345

Struggling to understand what you mean. You aren't looking for group by date_trunc('month', time)? — Craig Ringer
– Craig Ringer, Commented Oct 24, 2012 at 23:23
I am trying to get the sub total, if I do date_trunc('month', time), I dont get what I want. I get a lesser number. — ronak
– ronak, Commented Oct 24, 2012 at 23:32

Craig Ringer · Accepted Answer · 2012-10-24 23:40:37Z

4

As best I can guess from your question and comments you want sub-totals of the distinct counts by month. You can't do this with group by date_trunc('month',time) because that'll do a count(distinct column) that's distinct across all days.

For this you need a subquery or CTE:

WITH day_counts(day,day_col_count) AS (
  select date_trunc('day', time), count(distinct column)
  from table_name group by 1
)
SELECT 'Day', day, day_col_count
FROM day_counts
UNION ALL
SELECT 'Month', date_trunc('month', day), sum(day_col_count)
FROM day_counts
GROUP BY 2
ORDER BY 2;

My earlier guess before comments was: Group by month?

select date_trunc('month', time), count(distinct column)
from table_name
group by date_trunc('month', time)
order by time

Or are you trying to include running totals or subtotal lines? For running totals you need to use sum as a window function. Subtotals are just a pain, as SQL doesn't really lend its self to them; you need to UNION two queries then wrap them in an outer ORDER BY.

edited Oct 24, 2012 at 23:40

answered Oct 24, 2012 at 23:24

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

4 Comments

ronak Over a year ago

Thanks Craig, this helped. I tweaked the query to get the total at the end.

Craig Ringer Over a year ago

@ronak Glad to help. In future it's worth posting some sample data (CREATE TABLE and INSERT statements), some hand-crafted sample expected output, and the PostgreSQL version, as that makes it much easier to answer questions like this.

rogerdpack Over a year ago

is this possible with a subquery?

Craig Ringer Over a year ago

@rogerdpack Sure, just transform the CTE term into a subquery in FROM.

Clodoaldo Neto · Accepted Answer · 2012-10-24 23:40:31Z

1

select
    date_trunc('day', time)::text as "date",
    count(distinct column) as count
from table_name
group by 1
union
select
    'Total',
    count(distinct column)
from table_name
group by 1, date_trunc('month', time)
order by "date" = 'Total', 1

answered Oct 24, 2012 at 23:40

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

ronak Over a year ago

This does not give me the correct total. I get 404 instead of 872 which is what I expect.

Collectives™ on Stack Overflow

function to calculate aggregate sum count in postgresql

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related