Using MIN(), MAX() in AVG() aggregate.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using MIN(), MAX() in AVG() aggregate.

Author

Topic

emmodnar
Starting Member

7 Posts

Posted - 2011-01-11 : 23:07:56

Hello and thanks for reading this.

I need to use the MIN() and MAX() functions to pull certain rows from a table, then use the dates in those rows in an AVG() function.

So, I have something like this:

MIN(poneRE.ACCEPTDATE) as 'Ed Accept',
MIN(poneAA.RSTART) as 'Rev Invited',

--Problem -- Can't use MIN and MAX in aggregate function...
AVG(ABS(DATEDIFF(Day, MIN(poneRE.ACCEPTDATE), GETDATE()))-ABS(DATEDIFF(Day, MIN(poneAA.ACCEPTDATE), GETDATE()))) as 'Avg days ed. accpt to 1st rev inv. ORI.'

Of course, this generates a nice error, ERROR: Cannot perform an aggregate function on an expression
containing an aggregate or a subquery.
Error Code: 130

Does anyone have a suggestion for getting around this?

Thanks!

emmodnar

emmodnar
Starting Member

7 Posts

Posted - 2011-01-11 : 23:09:18

NOTE - this is all within a SELECT statement.

emmodnar

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2011-01-11 : 23:41:13

for example..


SELECT AVG(t.avg_Days)
FROM
(
SELECT COUNT(total_days) AS avg_Days
FROM dbo.User_table
) T
GO

-------------------------
/R..

emmodnar
Starting Member

7 Posts

Posted - 2011-01-12 : 11:36:56

Not sure this helps me... Anyone else?

emmodnar

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-12 : 11:54:35

Hard to guess at what you are doing. If you care to provide DDL, DML and expected output I'm sure we can help. Here is a link that can help you prepare this information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, as Rajdaksha suggested you could use a derived table (or inline view) to do some aggregation and then use that in an expression.

SELECT
    AVG(ABS(DATEDIFF(Day, MinREDate, GETDATE()))-ABS(DATEDIFF(Day, MinAADate, GETDATE()))) as 'Avg days ed. accpt to 1st rev inv. ORI.'
FROM
(
    SELECT 
        MIN(poneRE.ACCEPTDATE) AS MinREDate,
        MIN(poneAA.ACCEPTDATE) AS MinAADate
    FROM
        poneRE,  poneAA 
        ...
) AS T

emmodnar
Starting Member

7 Posts

Posted - 2011-01-12 : 12:08:59

Thanks -- I'll look at this a little more closely... obviously, I'm in the "New to SQL Server Programming" for a reason. FYI, I do not have permission to create a table or view on this server.

emmodnar

emmodnar
Starting Member

7 Posts

Posted - 2011-01-12 : 23:23:44

OK, tried this and got something... but I don't know what. I need to be able to output some of the data from the tables to see what I'm looking at...

This --

SELECT
AVG(ABS(DATEDIFF(Day, MinREDate, GETDATE()))-ABS(DATEDIFF(Day, MinAADate, GETDATE()))) as 'Avg days ed. accpt to 1st rev inv. ORI.'
FROM

(
SELECT
MIN(poneRE.ACCEPTDATE) AS MinREDate,
MIN(poneAA.ACCEPTDATE) AS MinAADate
FROM PONE.dbo.DOCUMENT pone
JOIN PONE.dbo.ROLEREVU_DOC_PEOPLE_ADDR_AUTH_ED poneAA ON poneAA.DOCUMENTID = pone.DOCUMENTID and poneAA.REVISION = pone.REVISION
JOIN PONE.dbo.SUGGEST_EDITOR_QUEUE poneSQ2 ON poneSQ2.PEOPLEID = poneAA.REVIEWER_PEOPLEID
JOIN PONE.dbo.ROLEEDIT poneRE ON poneRE.DOCUMENTID = pone.DOCUMENTID and poneRE.REVISION = pone.REVISION
WHERE poneAA.REVISION = 0 and poneSQ2.NOT_INVITED = 0 and poneSQ2.INVITE_DATE BETWEEN @DateBegin and @DateEnd
) AS T

;

Gave me this --
Avg days ed. accpt to 1st rev inv. ORI.
113

I need to get multiple rows and be able to show other columns from the tables.

emmodnar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources