0

I want to write a query using DATEDIFF Function in SQL.

When I try

SELECT AVG(DATEDIFF(hh,raise_date,(select min(added_note) from t_case_note) ))
FROM mytable

I get the error message

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

How can I achieve this?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
SanDeep KumAr
  • 29
  • 2

2 Answers2

2

Following clarification in the comments this should do what you need.

WITH CTE
     AS (SELECT min(added_note) AS min_added_note
         FROM   t_case_note)
SELECT AVG(DATEDIFF(hh, raise_date, CTE.min_added_note))
FROM   your_table
       CROSS JOIN CTE
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • +1 This works around the `Cannot perform an aggregate function on an expression containing an aggregate or a subquery.` error you'd get if you put the `min()` in scalar subquery – Andomar Jan 15 '13 at 13:06
1

You can do precisely that, see SQL Fiddle:

select  datediff(s,getdate(),(select min(dt) from t1))
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 1
    @MartinSmith: I thought the problem was in passing a scalar query, but it seems that is actually allowed. Updated the answerr, interesting! – Andomar Jan 15 '13 at 12:30
  • I am getting values in negative . and i want to add that i want to use AVG Funtion with this DATEDIFF i.e AVG(DATEDIFF(hh,raise_date,(select min(added_note) from t_case_note))..then i am getting error. – SanDeep KumAr Jan 15 '13 at 12:32
  • 1
    @MartinSmith: I'm certainly learning things by answering this question. There are also questions that are too simple for me, like this one must be for you, but those you can just avoid. – Andomar Jan 15 '13 at 12:47
  • actually as I added in the case notes when I am using it with AVG() function i am getting an error that "cannot peroform an aggregate function on an subquery" AVG(DATEDIFF(hh,raise_date,(select min(added_note) from t_case_note) )). Is there any alternative for this – SanDeep KumAr Jan 15 '13 at 12:51
  • @SanDeepKumAr - I didn't notice that you had edited your comment to say you were trying to use it in an `AVG`. I have edited your question to make this clear and submitted an answer that should work for you. – Martin Smith Jan 15 '13 at 13:02