0

I have the following query:

SELECT
EXTRACT (
    HOUR
    FROM
        interventions.created_at :: TIMESTAMP
) AS DATE,
COUNT (interventions. ID) AS total
FROM
    "interventions"
INNER JOIN medical_records ON (
    (
        medical_records.medical_recordable_type = 'Intervention'
    )
    AND (
        medical_records.medical_recordable_id = interventions. ID
    )
)
INNER JOIN patients ON (
    patients. ID = medical_records.patient_id
)
WHERE
    (
         interventions.created_at BETWEEN '2011-11-10 00:00:00'
         AND '2014-11-10 00:00:00'
    )
AND (
    medical_records.hospitalization = FALSE
)
AND (
    patients.birth_date BETWEEN '1892-12-31 23:50:39'
    AND '2013-12-31 22:59:59'
)
GROUP BY
    DATE
ORDER BY
    interventions. ID DESC,
    DATE ASC

And I get the following error:

PG::GroupingError: ERROR:  column "interventions.id" must appear in the GROUP BY clause or be used in an aggregate function

Here is the relevant part of my code :

query = query.select("EXTRACT(HOUR FROM interventions.created_at::timestamp) AS date, COUNT(interventions.id) AS total")
query = query.group("EXTRACT(HOUR FROM interventions.created_at::timestamp)")
query = query.order("EXTRACT(HOUR FROM interventions.created_at::timestamp) ASC")

I don't understand because the column "interventions.id" is used in the COUNT() function inside the select.

Any idea on how to resolve this issue?

Thank you.

4
  • Similar issue at stackoverflow.com/questions/16418504/pgerror-in-group-by-clause, it's better to check it out... Commented Nov 10, 2013 at 18:07
  • I already use a custom select, I added my code ;-) Commented Nov 10, 2013 at 18:12
  • Did you mean to say consultations in your code vs. interventions? Commented Nov 10, 2013 at 18:18
  • Oops yes sorry, I'll fix it! Commented Nov 10, 2013 at 18:29

1 Answer 1

1

Remove interventions_id from ORDER BY clause.

Sign up to request clarification or add additional context in comments.

1 Comment

In fact the ORDER BY interventions.idwas added by default_scope order('interventions.id DESC')` in my model. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.