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

Postgresql – How to Distinctly divide the employees added in a week and then get count of employees added on the a week day

I am looking for Postgresql Query to implement something like this.
I have to distinctly divide employee table on base of week and then get count of employees added on the a week days.

Example ::

Week                        | Sunday | Monday | Tueday | Wednesday | Thrusday | Friday |  Saturday |    Total   |
11/26/2023 - 12/02/2023     | 27     | 26     | 27     | 25        | 27       | 27     | 28        | 187        |
12/03/2023 - 12/09/2023     | 27     | 28     | 29     | 26        | 27       | 27     | 27        | 191        |
12/10/2023 - 12/16/2023     | 27     | 0      | 0      | 0         | 0        | 0      | 0         | 27         |

Column Explaination :

Week Column : [Start Date of the Week] - [End Date of the Week]
Sunday Column : Count of Employees Added on Sunday.
Monday Column : Count of Employees Added on Monday.
...
Total Column : Total Number of Employee on that Week.

Tables :

"Employee"

"Id" | "Name" | "Department" | "JoiningEpoch" |    "JoiningDate"    | "CreatedEpoch" |
 1   | Robin  |   Developer  |   1702706400   | 2023-12-16 00:00:00 |   1702706400   |
 2   | Emp2   |    HR        |   1702965600   | 2023-12-09 00:00:00 |   1702965600   |
 3   | Emp3   |   DevOps     |   1701237600   | 2023-11-29 00:00:00 |   1701237600   |
Tags:

2

Answers


  1. 0

    To get the count of employees added on each weekday for each week, you can use the date_trunc() function to group the employees by week and weekday, and then use the count() function to count the number of employees in each group. Something like this:

    SELECT
  date_trunc('week', JoiningDate) AS week,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 0) AS sunday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 1) AS monday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 2) AS tuesday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 3) AS wednesday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 4) AS thursday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 5) AS friday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 6) AS saturday,
  count(*) AS total
FROM employee
GROUP BY week
ORDER BY week;

    See if it works!

    Login or Signup to reply.
  2. 0

    You could use an aggregate filter clause to get day counts in separate columns, shift from ISO8601 Monday week start back one day to see your week ranges start on Sundays by subtracting 1 and use to_char() or set your DateStyle setting to achieve the desired date format: Demo at db<>fiddle

    set DateStyle='SQL,MDY';

SELECT
  to_char("Week",'MM/DD/YYYY')||' - '||("Week"::date+6)::text AS "Week",
  count(*)filter(where dow=0) AS "Sunday",
  count(*)filter(where dow=1) AS "Monday",
  count(*)filter(where dow=2) AS "Tuesday",
  count(*)filter(where dow=3) AS "Wednesday",
  count(*)filter(where dow=4) AS "Thursday",
  count(*)filter(where dow=5) AS "Friday",
  count(*)filter(where dow=6) AS "Saturday",
  count(*) AS "Total"
FROM (SELECT date_trunc('week', "JoiningDate")::date-1 AS "Week", 
             extract(DOW FROM "JoiningDate") dow 
      FROM "Employee")AS a
GROUP BY 1 ORDER BY "Week";
    Week Sunday Monday Tuesday Wednesday Thursday Friday Saturday Total
    11/26/2023 – 12/02/2023 0 0 0 1 0 0 0 1
    12/03/2023 – 12/09/2023 0 0 0 0 0 0 1 1
    12/10/2023 – 12/16/2023 0 0 0 0 0 0 1 1

    There’s also a crosstab() function in tablefunc extension that can pivot results this way automatically.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search