[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

aggregate function

Posted on 2005-04-08
4
Medium Priority
?
494 Views
Last Modified: 2012-06-27
Hi,

How come sql server says this is aggregate ? ExceptionTime is using sum()

Please kindly help me rewrite the query to work

Thanks.

Server: Msg 130, Level 15, State 1, Procedure SP_temp_shift_sys_nosched, Line 17
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Server: Msg 156, Level 15, State 1, Procedure SP_temp_shift_sys_nosched, Line 21
Incorrect syntax near the keyword 'GROUP'.

SELECT operatorid AS operatorid,sum(round(datediff(n,min(timein),max(timeout))/60.00,2)) as ExceptionTime
INTO #tempNoSchedexception
FROM inout
WHERE timein Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and timeout Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and excuse <> ' '
and operatorid in ( select distinct operatorid from #tempNoSchedinout)
GROUP BY operatorid

table called inout
timein
timeout
operatorid
0
Comment
Question by:lynnton
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
Nievergelt earned 1600 total points
ID: 13740050
You cannot use MIN and MAX aggregate functions in aggregate function SUM.

I am not sure what you want to accomplish, but I supect something like this:

SELECT operatorid AS operatorid,sum(round(datediff(n, (SELECT MIN(timein) FROM inout ),  (SELECT MAX(timeout) FROM inout ))/60.00,2)) as ExceptionTime
INTO #tempNoSchedexception
FROM inout
WHERE timein Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and timeout Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and excuse <> ' '
and operatorid in ( select distinct operatorid from #tempNoSchedinout)
GROUP BY operatorid
0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 400 total points
ID: 13740290
Nievergelt is right - ANSI standard SQL does not allow you to mix aggregate functions. They are functions used to get summary values. You would apply aggregates to a set of rows (all or based on your WHERE clause or based on your GROUP BY clause). No matter how you structure the sets you would only return a single value for each set of rows.

0
 
LVL 1

Author Comment

by:lynnton
ID: 13742786
I think this is the one.

SELECT operatorid AS operatorid,sum(round(datediff(n,timein,timeout)/60.00,2)) as ExceptionTime INTO #tempNoSchedexception
FROM inout
WHERE timein Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and timeout Between @shiftdate and (@shiftdate + cast('23:59:59' as datetime))
and excuse <> ' '
and operatorid in ( select distinct operatorid from #tempNoSchedinout)
GROUP BY operatorid
0
 
LVL 7

Expert Comment

by:Nievergelt
ID: 13742952
Yes, that makes certainly more sense than what I came up with, but I just wanted to show you a way to keep the MIN and MAX ;-).
However, I wonder, if would not better use seconds to get better precision:
SUM(ROUND(DATEDIFF(second, timein, timeout)/3600.0, 2))

Share and Enjoy  Christoph

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question