1 Reply Latest reply: Apr 1, 2010 2:54 PM by Frank Kulash RSS

    Not a single group function?

    733010
      I'm trying to do the following

      I have a table X being returned that looks like this:

      Col1 Col2
      200 0
      201 1
      204 0
      205 1
      etc


      I'm doing
      WITH mytable AS (SELECT Col1 as someID, sum(case when Col2 = something THEN 1 ELSE 0) as val FROM X) -- Summing all of the values of Col2 together to put a 2 in every row.
      SELECT someFunction(someID) + (select val from mytable) -- I want the value from that column, doesn't matter which row it comes from

      mytable: (produced from the WITH)
      someID val
      200 2
      201 2
      204 2
      205 2

      How do I do that? It keeps saying "Not a single group function". If I try to pick the value from a specific row it also says "Not a single group function".

      Edit:
      This seems to work..
      SELECT someFunction + sum(case when rownum < 2 then val else 0)

      But is there any normal way of doing what I need?

      Edited by: user12191393 on Apr 1, 2010 11:35 AM
        • 1. Re: Not a single group function?
          Frank Kulash
          Hi,

          Never write, let alone post, unformatted code.
          Use whitespace, especially indentation, to show the extent of, and major parts of, sub-queries, CASE expressions, and complicated function calls.

          When posting any formatted text on this site, type these 6 characters:

          \
          (small letters only, inside curly brackets) before and after each section of formatted text.
          
          The code you posted whould be much more readable that way.  For example:
          WITH mytable     AS
          (     SELECT Col1          as someID
               ,     sum ( case
                    when Col2 = something
                         THEN 1
                         ELSE 0
                    )      as val
               FROM X
          ) -- Summing all of the values of Col2 together to put a 2 in every row.
          SELECT someFunction(someID) + ( select val
                              from      mytable
                         ) -- I want the value from that column, doesn't matter which row it comes from
          Remember the ABC's of GROUP BY:
          When you use a GROUP BY clause and/or an aggregate fucntion, then everything in the SELECT clause must be:
          (a) an <b>A</b>ggregate function, 
          (b) one of the "group <b>B</b>y" expressions,
          (c) a <b>C</b>onstant, or
          (d) something that <b>D</b>epends entirely the above. (For example, if you "GROUP BY TRUNC(dt)", you can SELECT "TO_CHAR (TRUNC(dt), 'Mon-DD')").
          You're using an aggregate function in sub-query my_table, but col1 is not an aggregate, a GROUP BY expression, a constant, or any combination of them.
          You probably meant to say "GROUP BY col1" at the end of that sub-query.
          
          Don't you need an END to the CASE expression?
          
          Every query needs a FROM clause.  Your main query does not have one.  Perhap the end of the code got cut off when you posted it, or perhaps the scalar sub-query at the end is supposed to be part of the main query.
          
          If you need help, post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data (inside \
          tags).