Question 262810 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRESQL: Count the number of transactions of each extracted month from timestamp for each customer account?

I am having difficulty in attaining what I need. I have come across crosstab and pivot of which are not working in pycharm or maybe postgres no longer uses them. The query for summing works fine but when I try doing the same with COUNT() it instead brings same result for all months, even those with zero counts.


SELECT institution_account,
        COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount ELSE 0 END) AS Jan,
        COUNT(CASE WHEN extract('month' FROM date) = 2 THEN amount ELSE 0 END) AS Feb,
        COUNT(CASE WHEN extract('month' FROM date) = 3 THEN amount ELSE 0 END) AS Mar,
        COUNT(CASE WHEN extract('month' FROM date) = 4 THEN amount ELSE 0 END) AS Apr,
        COUNT(CASE WHEN extract('month' FROM date) = 5 THEN amount ELSE 0 END) AS May,
        COUNT(CASE WHEN extract('month' FROM date) = 6 THEN amount ELSE 0 END) AS Jun,
        COUNT(CASE WHEN extract('month' FROM date) = 7 THEN amount ELSE 0 END) AS Jul,
        COUNT(CASE WHEN extract('month' FROM date) = 8 THEN amount ELSE 0 END) AS Aug,
        COUNT(CASE WHEN extract('month' FROM date) = 9 THEN amount ELSE 0 END) AS Sep,
        COUNT(CASE WHEN extract('month' FROM date) = 10 THEN amount ELSE 0 END) AS Oct,
        COUNT(CASE WHEN extract('month' FROM date) = 11 THEN amount ELSE 0 END) AS Nov,
        COUNT(CASE WHEN extract('month' FROM date) = 12 THEN amount ELSE 0 END) AS Dec,
        COUNT(Amount) AS Total
   FROM transactioning
  GROUP BY institution_account

I need some help here, urgently. Thanks

Tags:

2

Answers


  1. 0

    The problem with your pivot query is that the COUNT functions counts 0 as 1. So change:

    COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount ELSE 0 END)

    to this:

    COUNT(CASE WHEN extract('month' FROM date) = 1 THEN amount END)

    If you want to use if/else then use SUM() as follows:

    SUM(CASE WHEN extract('month' FROM date) = 1 THEN amount ELSE 0 END)

    But note that from Postgres 9.4, the FILTER clause is supported. So I would write your full query as this version:

    SELECT
    institution_account,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 1) AS Jan,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 2) AS Feb,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 3) AS Mar,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 4) AS Apr,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 5) AS May,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 6) AS Jun,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 7) AS Jul,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 8) AS Aug,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 9) AS Sep,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 10) AS Oct,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 11) AS Nov,
    COUNT(amount) FILTER (WHERE extract('month' FROM date) = 12) AS Dec
    COUNT(Amount) AS Total
FROM transactioning
GROUP BY institution_account
    Login or Signup to reply.
  2. 0

    The issue you’re encountering with your query is related to the use of the COUNT function. When you use COUNT, it counts the number of rows, and it does not distinguish between the values of amount and 0. This is why you’re getting the same result for all months, including those with zero counts.
    To achieve your desired result, where you want to count the number of rows for each month while handling the zero counts correctly: you can update the query to

    SELECT institution_account,
        SUM(CASE WHEN extract('month' FROM date) = 1 THEN 1 ELSE 0 END) AS Jan,
        SUM(CASE WHEN extract('month' FROM date) = 2 THEN 1 ELSE 0 END) AS Feb,
        SUM(CASE WHEN extract('month' FROM date) = 3 THEN 1 ELSE 0 END) AS Mar,
        SUM(CASE WHEN extract('month' FROM date) = 4 THEN 1 ELSE 0 END) AS Apr,
        SUM(CASE WHEN extract('month' FROM date) = 5 THEN 1 ELSE 0 END) AS May,
        SUM(CASE WHEN extract('month' FROM date) = 6 THEN 1 ELSE 0 END) AS Jun,
        SUM(CASE WHEN extract('month' FROM date) = 7 THEN 1 ELSE 0 END) AS Jul,
        SUM(CASE WHEN extract('month' FROM date) = 8 THEN 1 ELSE 0 END) AS Aug,
        SUM(CASE WHEN extract('month' FROM date) = 9 THEN 1 ELSE 0 END) AS Sep,
        SUM(CASE WHEN extract('month' FROM date) = 10 THEN 1 ELSE 0 END) AS Oct,
        SUM(CASE WHEN extract('month' FROM date) = 11 THEN 1 ELSE 0 END) AS Nov,
        SUM(CASE WHEN extract('month' FROM date) = 12 THEN 1 ELSE 0 END) AS Dec,
        COUNT(*) AS Total
   FROM transactioning
  GROUP BY institution_account;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search