Postgresql Count Function Assistance

Question

In postgres table named "Transactions", I am trying to count the number of J-type transactions for each month. The following is my query.

select to_char("Date", 'Mon/YYYY') as "Date",
sum(case when l_part LIKE 'J%' then count end) as "IDs"
  from (
         select left("Type",4)as l_part, count(*),"Date" from 
         "Transactions" group by "Date",l_part
        ) p group by "Date"

  order by min("Date");

However, several problems occur with my query.

1) The number counted accumulates, so that every month's count also adds in the total count in all the months that have come before. However, I am just trying to count each month individually.

2) With this query, my output repeats some months (i.e., May 2015 my have 3 rows, 2 rows are empty, and 1 row has the actual count)

Any insights would be appreciated.

AlVaz · Accepted Answer · 2015-10-12 18:06:33Z

1

I don't think you need the subselect at all. Try this:

SELECT
  to_char("Date", 'Mon/YYYY') AS d,
  count(*) AS "IDs"
FROM "Transactions"
WHERE "Type" LIKE 'J%'
GROUP BY d

answered Oct 12, 2015 at 18:06

AlVaz

7661 gold badge6 silver badges21 bronze badges

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

1 Comment

Piechartking Over a year ago

I started to comment but I realized that I need to give more context in the original post, so I will repost in a different way. Thank you.

Gabriel's Messanger · Accepted Answer · 2015-10-12 18:34:52Z

1

I think it simpler to do this without subquery:

SELECT date_trunc('month', "Date"), count(*)
FROM "Transactions"
WHERE "Type" LIKE 'J%'
GROUP BY date_trunc('month', "Date");

Edited:

The date_trunc('month', ...) function truncates date to first day of its month ex. both '2015-May-26' and '2015-May-09' becomes '2015-May-01' etc. I've used this function instead of to_char because it's my habit since grouping by text could be slower than grouping by date. Of course it depends on size of "Transactions" table.

edited Oct 12, 2015 at 18:34

answered Oct 12, 2015 at 18:05

Gabriel's Messanger

3,37822 silver badges32 bronze badges

5 Comments

AlVaz Over a year ago

the date trunc isn't enough. OP is grouping by month/year

Gabriel's Messanger Over a year ago

Ok, but I think it's not so important since my answer also "count the number of J-type transactions for each month" and date_trunc is more natural for my in such a case.

AlVaz Over a year ago

The problem being that it doesn't retain the OP's group by logic so if your code was used it would output something quite different from the goal

user330315 Over a year ago

@AlVaz: grouping by date_trunc('month',...) will group by month/year and is essentially the same as your solution - it just uses the first day of each month for the output.

Gabriel's Messanger Over a year ago

@AlVaz there's nowhere said that to_char("Date", 'Mon/YYYY') is desired output. There's possibility that Piechartking didn't know about date_trunc and use string groupping with to_char (which could by slower). In terms of logic my solution gives exacly the same result as yours. I doubt there's reason to downvote.

Collectives™ on Stack Overflow

Postgresql Count Function Assistance

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related