CODEWITHSUNDEEP
sqloracle-databasegroup-by
ofince
ofince

Reputation: 193

Oracle SQL - error when using GROUP BY

select i.sicil_no,
       m.adi,
       m.soyadi,
       i.net_tutar,
       i.odeme_tarihi 
  from ibrmt050 i,
       mismt301 m 
where (i.sicil_no=m.sicil_no and 
       odeme_turu='36'       and 
       odeme_tarihi between '01/01/2012' and '30/06/2012')
GROUP BY TO_CHAR(i.odeme_tarihi,'MM') ,
         To_CHAR(i.odeme_tarihi,'YYYY') 
ORDER BY TO_CHAR(i.odeme_tarihi,'YYYY') ,
         TO_CHAR(i.odeme_tarihi,'MM');

I want to list per by month this query but it gives error.

"not a GROUP BY expression"

what am I supposed to do ?

Upvotes: 0

Views: 63

Answers (1)

krokodilko
krokodilko

Reputation: 36137

According to the documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm

Restrictions on the Select List
The select list is subject to the following restrictions:

If you also specify a group_by_clause in this statement, then this select list can contain only the following types of expressions:

  • Constants
  • Aggregate functions and the functions USER, UID, and SYSDATE
  • Expressions identical to those in the group_by_clause. If the group_by_clause is in a subquery, then the GROUP BY columns of the
    subquery must match the select list of the outer query. Any columns
    in the select list of the subquery that are not needed by the GROUP
    BY operation are ignored without error.
  • Expressions involving the preceding expressions that evaluate to the same value for all rows in a group

The select statements of your query contains the following columns:

select i.sicil_no,
       m.adi,
       m.soyadi,
       i.net_tutar,
       i.odeme_tarihi

that are not listed in the GROUP BY list, and this is a source of the error.

Upvotes: 1

Related Questions