Oracle SQL - error when using GROUP BY

Question

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 ?

What do you want to aggregate per month / what is your input and expected output? Please provide example data. — Frank Schmitt
– Frank Schmitt, Commented Aug 26, 2013 at 8:08
Search for oracle not a group by expression here on SO. There are a lot of questions concerning this. — user330315
– user330315, Commented Aug 26, 2013 at 9:22

krokodilko · Accepted Answer · 2013-08-26 09:09:50Z

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.

Collectives™ on Stack Overflow

Oracle SQL - error when using GROUP BY

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related