Please help me with a sum in a query 2

[sql99]

Hello,

I have the following script:

SELECT abc.employee, fdb.workday, abc.name, vew.program
FROM employee abc, schedule fdb, group vew
WHERE emp.prog = 'TEST'
AND vew.plan in ('ABC', 'ABB', 'AAB')
AND vew.date = to_date('03/15/1999', 'mm/dd/yyyy')
AND sum(fdb.hours) > '0.00'
AND abc.emply_num = fdb.emply_num
AND abc.period = fdb.period
AND fdb.number = vew.number


I would like to get the records displayed for hours in the schedule table totalling to more than 0.00 but it tells me "group function is not allowed here". Can someone point me to how I can accomplish this? I can't seem to get the sum function to work.

Thanks in advance,
sql99

 

[Turkbear]

Hi, Try this:

SELECT abc.employee, fdb.workday, abc.name, vew.program,
sum(fdb.hours)
     FROM employee abc, schedule fdb, group vew
    WHERE emp.prog = 'TEST'
      AND vew.plan in ('ABC', 'ABB', 'AAB') 
      AND vew.date = to_date('03/15/1999', 'mm/dd/yyyy')
      AND abc.emply_num = fdb.emply_num
      AND abc.period = fdb.period
      AND fdb.number = vew.number
Group by abc.employee, fdb.workday, abc.name, vew.program
Having sum(fdb.hours) > 0;

 

[SantaMufasa]

SQL99,

Yes, we absolutely can help you.

First, however, it is important to know that once you refer to a "GROUPed" expression [such as SUM(<exp>)]in either a SELECT clause, then every expression in the SELECT clause must also be a GROUPed expression. The way to turn the other expressions into GROUP expressions is to mention them in a GROUP BY clause (which you are missing).

Also, the way that we can then filter resulting rows by "sum(fdb.hours) > '0.00' " is via the "HAVING" clause instead of the "WHERE" clause.

So, the quick (syntactically accommodating) method to resolve you problem is to say:

SELECT abc.employee, fdb.workday, abc.name, vew.program
     FROM employee abc, schedule fdb, group vew
    WHERE emp.prog = 'TEST'
      AND vew.plan in ('ABC', 'ABB', 'AAB') 
      AND vew.date = to_date('03/15/1999', 'mm/dd/yyyy')
      AND abc.emply_num = fdb.emply_num
      AND abc.period = fdb.period
      AND fdb.number = vew.number
    GROUP BY abc.employee, fdb.workday, abc.name, vew.program
    HAVING sum(fdb.hours) > '0.00';

Now, if you run the above code and it "works", but it doesn't give you the results you wanted, then post back to this thread (with your "SELECT" statement, the results, and a critique of what you want different).

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:22 (24Nov04) UTC (aka "GMT" and "Zulu"),
@ 14:22 (24Nov04) Mountain Time

 

[sql99]

Thank you Turkbear and Dave....Indeed, I was missing the Group by. After adding it, everything worked fine. Thanks again for your help....Happy Thanksgiving!