Column must appear in the GROUP BY clause

Get Help
1

I’m trying to group results by Year Month…

select
to_char(simpro_jobs.date_created, ‘YYYY MM’) as “Month”,
default_name as “Job Type”,
count(default_name) as Jobs
from
simpro_job_cost_centres
inner join simpro_jobs on simpro_jobs.job_id = simpro_job_cost_centres.job_id
where
simpro_jobs.date_created between ‘2020-01-01’ and NOW()
group by
to_char(simpro_jobs.date_created, ‘YYYY MM’),
simpro_job_cost_centres.default_name
order by
simpro_jobs.date_created ASC,
count(default_name) DESC;

But I get this error…
org.postgresql.util.PSQLException: ERROR: column “simpro_jobs.date_created” must appear in the GROUP BY clause or be used in an aggregate function Position: 548

I’m not sure how to get this to work.
I welcome any suggestions.

2

Hi @Carl
You’ll find much better help with regular SQL questions on stackoverflow.com or similar forums.
Change simpro_jobs.date_created ASC to to_char(simpro_jobs.date_created, 'YYYY MM') ASC

3

Hi @flamber

Thank you for your suggestion.
It was how I wrote the query originally, but for some reason it is not working.
As you can imagine, I have tried all sorts of variations and combinations, but something is just not right.

I will take this issue over to stackoverflow.com as you recommend.

Thanks again.

4

Whenever I write any SQL, I always find it’s much easier to debug if I use a query tool first. For MS SQL, I use SQL Management studio. For MySql the workbench.
If I’m feeling lazy, I just fire up Crystal Reports, take the SQL it generates then remove all the aliasing.