Thread: Group By

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Group By


    Hi,

    I have a SELECT statement with problems. It's large, but it's not complex at all. Here you go:



    Code:
    SELECT r.id,
           r.project,
           p.name,
           r.topic,
           r.priority,
           r.requester,
           u_req.fullname,
           r.requester_ext,
           r.client,
           r.executor,
           u_ex.fullname,
           r.supervisor,
           u_sup.fullname,
           r.tester,
           u_test.fullname,
           r.time_start,
           r.time_end,
           r.status,
           (select status_n from request_status(r.status)),
           (select count(*) from sys$files where id=r.files),
           sum(iif(rd.new_status = 25, time_length, 0)) as validate_time,
           sum(iif(rd.new_status = 35, time_length, 0)) as analyze_time,
           sum(iif(rd.new_status = 65, time_length, 0)) as work_time,
           sum(iif(rd.new_status = 75, time_length, 0)) as test_time,
           sum(iif(rd.new_status between 90 and 99, time_length, 0)) as reject_count,
           sum(iif(rd.new_status = 80, 1, 0))
    FROM man$requests r
    JOIN man$requestprojects p ON (r.project = p.id)  
    LEFT JOIN sys$users u_req on (r.requester=u_req.id)
    LEFT JOIN sys$users u_ex on (r.requester=u_ex.id)
    LEFT JOIN sys$users u_sup on (r.requester=u_sup.id)
    LEFT JOIN sys$users u_test on (r.requester=u_test.id)
    JOIN man$requestdet rd on (rd.request = r.id)
    GROUP BY r.id

    I knew before compiling this statement that it will give me this error:

    Invalid token.
    Dynamic SQL Error.
    SQL error code = -104.
    Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).


    but as you can see i have 2 major tables here MAN$REQUESTS (r) and MAN$REQUESTDET (rd), the rest are just for gathering human readable information (converting IDs into names)

    and the simple thing this select has to do is to list different user's worktimes

    for every REQUEST i have some REQUESTDET details (there is stored what happened with the request)


    The problem:
    Firebird asks me to list all the statements in the GROUP BY clause that contain no aggregate functions

    I'd like to avoid that somehow, since i not only have simple fields there but also subselects.

    And i don't even understand why Firebird is so strict. In the old Foxpro ages that worked without problems.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Firebird asks me to list all the statements in the GROUP BY clause that contain no aggregate functions
    SQL specification I believe. Required in all SQL databases I know.
    See if using the field index numbers will work for grouping if the fields are complex sub queries. I do not think field aliases work for group by.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    Originally Posted by clivew
    SQL specification I believe. Required in all SQL databases I know.
    See if using the field index numbers will work for grouping if the fields are complex sub queries. I do not think field aliases work for group by.
    Doesn't work with index numbers. If there's no other solution i'll need to change it into a Stored Procedure, but that will work a little bit slowly

    or i can eliminate the GROUP BY clause and use subselects at the sum & count part...and that will also be slower ...ok i'll gve a try to see the performance difference at least
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Try turning the query into a view (without the GROUP BY of course) with field names for the sub-queries, then write your query against the view using the view column names in the GROUP BY clause.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    You can try to use MAX, something like this :

    SELECT r.id,
    max(r.project) as project,
    max(p.name) as name,
    max(r.topic) as topic,
    ......
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2008
    Posts
    55
    Rep Power
    7

    aggregate function or the GROUP BY clause


    all fields in SELECT clausule, except with functions Max, Sum, Min, AVG, etc... aggregate functions... should be placed in clause SELECT...

    so, you can to use the format "CET" to facilitate your work .


    -----------------------------------

    WITH
    tbNameX ( select .... from .... clausulesWhere,group....),
    tbNameY (....)


    select fields
    from tbNamex
    left outer join tbNameY on (....)
    ....clausuleswhere,group, etc....


    -----------------------------------

    remember: ALL tables CET should be in the clausule main SELECT


    search in goole for the book Helen Borrie : The Firebird Book: A Reference for Database for Developers

    great book!

IMN logo majestic logo threadwatch logo seochat tools logo