I have created a query in PgAdmin, where one column named 'joining_date' in output returns a date.

When I use Group By I would like using only MM_YYY instead of DD_MM_YYY which is output datatype of 'joining_date' so that same employee_type can be grouped together.

create table employee(joining_date date, 
employee_type varchar, name character varying);

insert into employee values
('16-11-2022', 'Intern', 'ABBS'),
('11-11-2022', 'senior', 'ABBS'),
('12-11-2022', 'senior', 'ABBS'),
('11-11-2022', 'senior', 'ABBS'),
('12-11-2022', 'Intern', 'ABBS');

select employee_type as emp, 
to_char(joining_date, 'MM_YY') as batch, 
count(employee_type) as num
from employee
GROUP BY employee_type, joining_date;

What I obtained is:

What I want to obtain is:

How to proceed?

CodePudding user response：

Your query is almost correct.

But you need to use the same formatting in selection and GROUP BY, otherwise your grouping has not the intended effect.

So if your selection should be done as TO_CHAR(joining_date, 'MM_YY'), this must be used in the GROUP BY clause, too.

SELECT employee_type AS emp, 
TO_CHAR(joining_date, 'MM_YY') AS batch, 
COUNT(employee_type) AS num 
FROM employee 
GROUP BY employee_type, 
TO_CHAR(joining_date, 'MM_YY');

You can replicate here that this will produce the expected result: db<>fiddle