CODEWITHSUNDEEP
sqloracle-database
Albert
Albert

Reputation: 13

'ora-00979'(not a group by expression) caused by 'order by'

I'm sorry for my poor English,but I will try my best to describe my question

SELECT user_id,
       Count(user_id),
       Trunc(created_time, 'dd')
FROM   (SELECT user_id,
               created_time
        FROM   t_vote_join_user
        WHERE  created_time >= To_date('20120720', 'yyyymmdd')
               AND created_time < To_date('20120814', 'yyyymmdd')
               AND ( ( vote_id = 1270
                       AND options = '["5526"]' )
                      OR ( vote_id = 1306
                           AND options = '["5666"]' )
                      OR ( vote_id = 1320
                           AND options = '["5759"]' )
                      OR ( vote_id = 1366
                           AND options = '["5966"]' )
                      OR ( vote_id = 1393
                           AND options = '["6066"]' )
                      OR ( vote_id = 1432
                           AND options = '["6203"]' )
                      OR ( vote_id = 1452
                           AND options = '["6269"]' )
                      OR ( vote_id = 1502
                           AND options = '["6482"]' )
                      OR ( vote_id = 1610
                           AND options = '["6863"]' )
                      OR ( vote_id = 1627
                           AND options = '["6917"]' )
                      OR ( vote_id = 1651
                           AND options = '["7010"]' )
                      OR ( vote_id = 1676
                           AND options = '["7098"]' ) ))
GROUP  BY user_id,
          Trunc(created_time, 'dd')
ORDER  BY Trunc(created_time, 'dd')

executed error caused by ora-00979(not a group by expression),but executed ok without 'order by TRUNC(created_time, 'dd')'

Upvotes: 1

Views: 2834

Answers (3)

schurik
schurik

Reputation: 7928

try to use the alias for the column

SELECT user_id,
       Count(user_id),
       Trunc(created_time, 'dd')  AS created_date
FROM   (SELECT user_id,
 ...

GROUP  BY user_id,
          Trunc(created_time, 'dd')
ORDER  BY created_date
;

Upvotes: 0

Lock
Lock

Reputation: 5522

Try the following for your order by: Order by 3

Upvotes: 1

Sunil Reddy
Sunil Reddy

Reputation: 1

When grouping, make sure the elements in the select string, not part of the group by, make use of an appropriate grouping or many-to-one function (ex: sum, avg etc). Please post the complete query to get a proper resolution.

Upvotes: 0

Related Questions