CODEWITHSUNDEEP
sqloracle-databasegroup-by
Antarr Byrd
Antarr Byrd

Reputation: 26169

Unable to group by date in Oracle query

I'm trying to group my results by due_date in Oracle. But I keep getting the error not a GROUP BY expression.

SELECT S.* FROM Shipments S
WHERE S.delivered = 'false' 
AND S.customer_id = 'mycustomerid'
AND S.due_date BETWEEN TO_DATE('2019-06-14','YYYY-MM-DD') AND TO_DATE('2019-06-18','YYYY-MM-DD')
GROUP BY TO_DATE(S.due_date,'YYYY-MM-DD')

Upvotes: 0

Views: 1601

Answers (2)

Francisco Sevilla
Francisco Sevilla

Reputation: 104

What you are attempting to do is an aggregation query which is required to use the aggregation functions AVG, COUNT, MAX, MIN, SUM in columns to get the expected results: 

SELECT TRUNC(S.due_date)
       ,COUNT(*)
       ,MAX(S.total_amount)
FROM Shipments S
WHERE S.delivered = 'false' AND
      S.customer_id = 'mycustomerid' AND
      S.due_date >= DATE '2019-06-14' AND
      S.due_date < DATE '2019-06-19'
GROUP BY TRUNC(S.due_date);

Assuming that total_amount is a column of Shipments table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

SELECT * and GROUP BY basically never make sense together. In an aggregation query, you have the columns (or expressions) in the GROUP BY. Everything else should be the argument to an aggregation function.

So, if you wanted counts by day, you would do:

SELECT TRUNC(S.due_date), COUNT(*)
FROM Shipments S
WHERE S.delivered = 'false' AND
      S.customer_id = 'mycustomerid' AND
      S.due_date >= DATE '2019-06-14' AND
      S.due_date < DATE '2019-06-19'
GROUP BY TRUNC(S.due_date)
ORDER BY TRUNC(S.due_date);

Notes:

  • due_date is -- presumably -- a date. You don't need to convert a date to a date. To remove the time component, use trunc().
  • Date comparisons shoud not use between. Time components can interfere with such logic. The above logic works for dates both with and without time components.
  • Date constants can be introduced with the date keyword and use the ISO standard YYYY-MM-DD format.

Upvotes: 2

Related Questions