CODEWITHSUNDEEP
postgresql
Piechartking
Piechartking

Reputation: 135

Postgresql Count Function Assistance

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.

Upvotes: 1

Views: 45

Answers (2)

Gabriel's Messanger
Gabriel's Messanger

Reputation: 3298

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.

Upvotes: 1

AlVaz
AlVaz

Reputation: 766

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

Upvotes: 1

Related Questions