CODEWITHSUNDEEP
postgresql
nqtien310
nqtien310

Reputation: 359

Total count for each month within period

Given this scenario , I have the following employment records

id | user_id   |  Month          | Active
1  | 1         |  June 2014      | true
2  | 1         |  September 2014 | false
3  | 2         |  June 2014      | true

How can i make a query to return the total active users for each month, the return data should be: 

active_count | month
2            | June 2014
2            | July 2014
2            | August 2014
1            | September 2014

Any help is highly appreciated

Upvotes: 0

Views: 70

Answers (2)

user330315
user330315

Reputation:

You are looking for a conditional aggregate:

SELECT count(case when active then 1 end) as active_count,
       month
FROM employment 
GROUP BY month;

With Postgres 9.4 this can be written a bit more concise using the filter() operator:

SELECT count(*) filter (where active) as active_count,
       month
FROM employment 
GROUP BY month;

Upvotes: 2

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

It is sql query try to this

SELECT  
    count(id) active_count,
    month
FROM
    employment 
GROUP BY
    month;

Upvotes: 0

Related Questions