ended up with null values
Then nothing is satisfying the case statement
or
C.StartChatTime or C.RequestedTime is null
I wonder if you could do something like
SUM(CASE WHEN c.status = 'active' or c.status = 'In Work'
THEN (C.StartChatTime - C.RequestedTime) * 24 * 60
ELSE 0
END) AS TotalValue,
SUM(CASE WHEN c.status = 'active' or c.status = 'In Work'
THEN 1
ELSE 0
END) AS CountOfItems
and then make your own average by dividing one by the other. I expect you should get the same result, but this way (if you output it as two values first, then modify the code to divide one by the other - and maybe also compare that against any value that you get from AVG() ) you can see what the intermediate values are, and from that figure out how to debug it if necessary.
e.g. If you get [CountOfItems] = 0 then none of the rows satisfied the
c.status = 'active' or c.status = 'In Work'
criteria, so you could then decide what to do about that.
This would, when you have it all working properly, also fix the issue of Warning about NULL that I raised earlier (although there is probably a better / smarter way to do that - personally I wouldn't know how to do it "smarter" in Oracle)
P.S. you might need to check if either C.StartChatTime or C.RequestedTime is NULL as that would propagate into the SUM'd or AVG'd result. If those columns were created as NOT NULL then I guess no such test is needed. Perhaps just COALESCE them to 0? (although IMO that will distort the AVERAGE by increasing the CountOfItems but not the TotalValues
You seem to be very, very confused. Did you know this is a SQL Forum, not Oracle?
There is no CASE statement in SQL. We have a CASE expression! This is a fundamental concept, regardless of what SQL product you have.
There is no such crap as a generic “status” in RDBMS-- marriage? Employment? Graduation?
I need to determine the average call time between two times when a status is either active or in work: <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
You do not know how to model an event, like a service call, with a pair of (start, end) timestamps. A NULL end timestamp would mean the event is still open. Download a free copy of the Snodgrass book on temporal SQL queries from the University of Arizona.
You also do know SQL shorthand. We would use the IN() predicate:
CASE WHEN service_call_status IN ('ACTIVE', 'IN WORK')
THEN ..
ELSE NULL END;
85 to 95% of the real work in SQL is in the DDL.
I will guess that those two service call status codes mean that the service_call_end_timestamp IS NULL to show it is unresolved. Oh, SQL Server does not use ANSI/ISO Standard temporal math; see why this is a bad place to ask your question? 
WITH Service_Call_Durations
AS
(SELECT service_ticket,
DATEDIFF (SECONDS, service_call_begin_timestamp,
COALESCE (service_call_end_timestamp,
CURRENT_TIMESTAMP) AS answer_duration)
FROM Foobar) --- we have no name! No DDL!
SELECT AVG(answer_duration) AS answer_duration_avg
FROM Service_Call_Durations;
while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused. I realized I posted to the wrong forum but not intentionally and I certainly do know the difference. I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the goru of programming. I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. I will not be posting again in this forum you have secured that decision with your ignorance.
thank you for the help I appreciate it.
To jcelko,
the above response was meant for you. No one in this thread deserved what you said to me.
thank you for your help
Is the below response really how this forum operates. OMG I am so insulted by ICELKO. Please realize how rude you are.
jcelko
19h
You seem to be very, very confused. Did you know this is a SQL Forum, not Oracle?
There is no CASE statement in SQL. We have a CASE expression! This is a fundamental concept, regardless of what SQL product you have.
There is no such crap as a generic “status” in RDBMS-- marriage? Employment? Graduation?
I need to determine the average call time between two times when a status is either active or in work: <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
You do not know how to model an event, like a service call, with a pair of (start, end) timestamps. A NULL end timestamp would mean the event is still open. Download a free copy of the Snodgrass book on temporal SQL queries from the University of Arizona.
You also do know SQL shorthand. We would use the IN() predicate:
CASE WHEN service_call_status IN ('ACTIVE', 'IN WORK')
THEN ..
ELSE NULL END;
85 to 95% of the real work in SQL is in the DDL.
I will guess that those two service call status codes mean that the service_call_end_timestamp IS NULL to show it is unresolved. Oh, SQL Server does not use ANSI/ISO Standard temporal math; see why this is a bad place to ask your question? 
thank you got it to work
while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused. I realized I posted to the wrong forum but not intentionally and I certainly do know the difference. I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the goru of programming. I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. I will not be posting again in this forum you have secured that decision with your ignorance
Definitely not.
Whilst Celko was an eminent authority on SQL, and has written many informative books, his posts are always rude and, these days, rarely contribute anything useful. He has been banned from the other SQL forums that I participate in, and had all his posts removed. He was banned from this one on the old software, but when the new software was installed and users invited to re-register that, sadly, included people who had been previously banned.
Why I don't understand, and you too I presume, is why ADMIN here still hasn't banned him..
Come to think of it, Celko seems to post each Saturday night ...
I'll second that
while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused.
Yes, it was. Otherwise, self-entitled noobs will not hear you today. Crap was the mild, socially acceptable term from Dilbert. And anyone who asks bad questions on the wrong forum (not just bad questions on the right forum!) is confused.
I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the guru of programming.
Get over yourself. This is the Internet, not some liberal college campus with safe speech zones, micro aggregations, and all that crap. And you are not a co-worker; you are asking for help as a student. I have actually had the job title “guru” with a company 
But there is no “as if” in me – did you Google me? Wikipedia me? I feel I have credentials and the documentation to support the opinion that I now more than you do.
I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question
I wrote the answers for software engineering (Google my work in the trade press in the early days of the Structured Programming revolution) and for SQL programming (10 years on ANSI X3H2, book series, 1200+ articles, etc)). But I also expect the person asking a question to follow forum rules.
Think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. <<
Yes, I made huge conceptual errors back in the 1960's. But I never did a “boo-hoo, you hurt my feelings!” rant like you. I loved it when I got a correction from a competent person-- the stronger and more detailed, the better. This I how adults and professionals handle their lives and careers.
Ever hear the name “Edsger W. Dijkstra” in school? Google it. He taught here at UT in Austin. His students were told over and over, “YOU ARE DOING IT COMPLETELY WRONG!!” I had the window when I moved to Austin to get a class under him and did not. I will regret that until my dying day.
I will not be posting again in this forum you have secured that decision with your ignorance. ]
My ignorance? Who posted what? Sorry you are determined not to learn. Oh, would you ask your boss to fire you with a bad reference in exchange for a week of me fixing your code? I have done this twice before, but in fairness, I knew the bosses and they were going to get rid of the employee anyway. They knew the incompetents were presenting NewGroup solutions as their own work.
Moderators, please. How long do we have to tolerate his rude behaviour?
2 Likes
Good Morning,
I hope you were not referring to me and if so and I was rude it was not meant towards you or anyone else in this forum. Joe Celko was the one that was so rude to me and he continued. I appreciate all your help but this definately was not me. This is what Joe Celko send to me this morning along with excerpts from my initial response to him.
I have always enjoyed this forum but I was really shocked at the way he handled my initial question. Please see below and if there is an administrator please point me in his direction so that I may complain. We are all learning in my opinion.
Me: while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused.
Joe:Yes, it was. Otherwise, self-entitled noobs will not hear you today. Crap was the mild, socially acceptable term from Dilbert. And anyone who asks bad questions on the wrong forum (not just bad questions on the right forum!) is confused.
Me: I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the guru of programming.
Joe: Get over yourself. This is the Internet, not some liberal college campus with safe speech zones, micro aggregations, and all that crap. And you are not a co-worker; you are asking for help as a student. I have actually had the job title “guru” with a company 
But there is no “as if” in me – did you Google me? Wikipedia me? I feel I have credentials and the documentation to support the opinion that I now more than you do.
Me: I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question
Joe: I wrote the answers for software engineering (Google my work in the trade press in the early days of the Structured Programming revolution) and for SQL programming (10 years on ANSI X3H2, book series, 1200+ articles, etc)). But I also expect the person asking a question to follow forum rules.
Me: Think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. <<
Joe: Yes, I made huge conceptual errors back in the 1960's. But I never did a “boo-hoo, you hurt my feelings!” rant like you. I loved it when I got a correction from a competent person-- the stronger and more detailed, the better. This I how adults and professionals handle their lives and careers.
Ever hear the name “Edsger W. Dijkstra” in school? Google it. He taught here at UT in Austin. His students were told over and over, “YOU ARE DOING IT COMPLETELY WRONG!!” I had the window when I moved to Austin to get a class under him and did not. I will regret that until my dying day.
Me:I will not be posting again in this forum you have secured that decision with your ignorance. ]
Joe:My ignorance? Who posted what? Sorry you are determined not to learn. Oh, would you ask your boss to fire you with a bad reference in exchange for a week of me fixing your code? I have done this twice before, but in fairness, I knew the bosses and they were going to get rid of the employee anyway. They knew the incompetents were presenting NewGroup solutions as their own work.
2 Likes
@maggie_patterson JCelko has been banned for 4,000 days ... I reckon the duration is a side effect of some SQL statement, which seems entirely appropriate ...
1 Like
Thank you so much Kristen. I code in both sql and oracle depending on the day and I was really shocked at how rude this guy is. I'm like really, then he sent me another response this morning. He even pulled excerpts from my response to him to continue his attacks. I think I am sending this to the Admin for this site. I am not seasoned and that is my reason for reaching out to the forum.
Maggie:while I appreciate your response I do not appreciate how rude it was. That was not necessary to use the terms crap or very very confused.
Celko:Yes, it was. Otherwise, self-entitled noobs will not hear you today. Crap was the mild, socially acceptable term from Dilbert. And anyone who asks bad questions on the wrong forum (not just bad questions on the right forum!) is confused.
Maggie:I am really surprised at the length of rudeness and I hope you don't treat your co workers this way as if you are the guru of programming.
Celko:Get over yourself. This is the Internet, not some liberal college campus with safe speech zones, micro aggregations, and all that crap. And you are not a co-worker; you are asking for help as a student. I have actually had the job title “guru” with a company But there is no “as if” in me – did you Google me? Wikipedia me? I feel I have credentials and the documentation to support the opinion that I now more than you do.
Maggie:I am sure you forget that at some point in time you did not know all the answers and I am sure you still don't know all the answers so before you comment on another person's question
Celko:I wrote the answers for software engineering (Google my work in the trade press in the early days of the Structured Programming revolution) and for SQL programming (10 years on ANSI X3H2, book series, 1200+ articles, etc)). But I also expect the person asking a question to follow forum rules.
Maggie:Think about how you were when you first started programming. and climb back down off that pedestal because I am sure that someone will knock you off of it at some point in time. <<
Celko:Yes, I made huge conceptual errors back in the 1960's. But I never did a “boo-hoo, you hurt my feelings!” rant like you. I loved it when I got a correction from a competent person-- the stronger and more detailed, the better. This I how adults and professionals handle their lives and careers.
Ever hear the name “Edsger W. Dijkstra” in school? Google it. He taught here at UT in Austin. His students were told over and over, “YOU ARE DOING IT COMPLETELY WRONG!!” I had the window when I moved to Austin to get a class under him and did not. I will regret that until my dying day.
Maggie:I will not be posting again in this forum you have secured that decision with your ignorance. ]
Celko:My ignorance? Who posted what? Sorry you are determined not to learn. Oh, would you ask your boss to fire you with a bad reference in exchange for a week of me fixing your code? I have done this twice before, but in fairness, I knew the bosses and they were going to get rid of the employee anyway. They knew the incompetents were presenting NewGroup solutions as their own work.
thanks for another comment from Kristen I realized you were speaking about Celko. Is there a place to report his behavior? I am just floored at the level of rudeness he sunk to.