Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    13

    Unanswered: Issue with subquery and ORA-00979 (not a group by expression)

    I'm trying to run the below query to retrieve the following:

    month/day/hour(24), total number of processed, total number of rejected

    When I run this I get the following error:
    ORA-00979: Not a group by expression

    select to_char(arrivaldate,'mmddHH24'),
    (select count(*) as Total from m_msg_condition mc where m.message_id=mc.message_id and status='PD') Processed,
    (select count(*) as Total from m_msg_condition mc where m.message_id=mc.message_id and status='RJ') Rejected
    from m_msg m
    where to_char(arrivaldate,'mmddHH24') >= '080100'
    group by to_char(arrivaldate,'mmddHH24')
    order by to_char(arrivaldate,'mmddHH24');
    Last edited by dmorand; 08-03-10 at 14:51. Reason: Fixed sql

  2. #2
    Join Date
    Aug 2010
    Posts
    13
    I know the issue is with the group by to_char(arrivaldate,'mmddHH24') because when I remove it the query runs, but obviously I don't get my desired results.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does this work?
    Code:
    SELECT TO_CHAR(m.arrivaldate,'mmddHH24')
         , COUNT(CASE WHEN mc.status='PD' 
                      THEN 'humpty' ELSE NULL END) Processed
         , COUNT(CASE WHEN mc.status='RJ' 
                      THEN 'dumpty' ELSE NULL END) Rejected
      FROM m_msg m
    LEFT OUTER
      JOIN m_msg_condition mc
        ON mc.message_id = m.message_id
     WHERE TO_CHAR(m.arrivaldate,'mmddHH24') >= '080100'
    GROUP 
        BY TO_CHAR(m.arrivaldate,'mmddHH24')
    ORDER 
        BY TO_CHAR(m.arrivaldate,'mmddHH24');
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2010
    Posts
    13
    Nice that worked, thanks!!

  5. #5
    Join Date
    Aug 2010
    Posts
    13
    Do you happen to know why my example above won't work? I'm just curious

  6. #6
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    It's because your GROUP BY clause does not contain all the expressions you have in your SELECT clause.
    http://www.dbmotive.com/oracle_error_codes.php?errcode=00979
    http://ora-00979.ora-code.com/

  7. #7
    Join Date
    Aug 2010
    Posts
    13
    Thanks for the info!

  8. #8
    Join Date
    Aug 2010
    Posts
    1
    Thanks for the information!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •