DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL Query Issues

  1. #1
    Join Date
    Sep 2005
    Posts
    2

    SQL Query Issues

    OK when trying to run the following query on oracle 8i I get an error. However when running on 9i it works fine. Unfortunately I have to get this to work on 8i for work.

    example query:

    Code:
    select fcr, count(fcr) from incidents group by fcr;
    
    FCR COUNT(FCR)
    --- ----------
    N/A 3466
    No 8844
    Yes 111968
    
    Now what I want is this
    
    FCR COUNT(FCR) % of total
    --- ---------- -------------
    N/A 3466 0.02
    No 8844 0.07
    Yes 111968 .91
    which should be possible with this query
    Code:
      SELECT fcr,
      (count(fcr)/(SELECT count(fcr) FROM incidents))
      FROM incidents
      GROUP BY fcr;
    But when running on oracle 8i I get the following error
    ORA-00979: not a GROUP BY expression
    and SQL*Plus shows the * under the count(fcr) within the nested select.

    Any Ideas how else this could be done or a way to not recieve this error?

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    Perhaps...
    Code:
    SELECT fcr, COUNT (fcr) / total total
    FROM  (SELECT i.*, COUNT (fcr) OVER () total
           FROM   incidents i)
    GROUP BY fcr, total;

  3. #3
    Join Date
    Sep 2005
    Posts
    2
    That worked great. Now can you explain a few things to me, please.

    1.) SELECT i.*
    2.) incidents i
    3.) over()

    Is the incidents i doing the same thing as count(fcr) total where it uses total as an alias, if so I think that would mean the i.* is selecting everything from incidents?
    OVER() not sure about

Posting Permissions

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


Click Here to Expand Forum to Full Width