July 13, 2012 at 5:26 am
Hi there,
I have the below query. I was using this for one team, but now it needs to be used across 3 teams and to include the team name. Before this I was using this query to get the average fix time of all calls across one team.
Here is my query:
select cast(AvgMins/60 as varchar(10)) + ':' + right('00' + cast(AvgMins%60 as varchar(10)), 2)
from
(Select sum(fix_time/60)/COUNT (*) as AvgMins, suppgroup
from calltable
where suppgroup in ('WEBS', 'Tech','SQL')
and status <>'17'
and Month (date_time) in ('4', '5', '6')
and YEAR (date_time) = ('2012'))[as Sub1;]
Group by suppgroup
I am getting the error
Msg 8120, Level 16, State 1, Line 3
Column 'calltable.suppgroup' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I cannot see the wood for the trees so any advice will be great on where I am going wrong.
Thank you in advance!
July 13, 2012 at 5:29 am
Hi
Your GROUP BY was outside your sub-query
SELECT CAST(AvgMins/60 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(AvgMins%60 AS VARCHAR(10)), 2)
FROM
(SELECT SUM(fix_time/60)/COUNT (*) AS AvgMins, suppgroup
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND status <>'17'
AND MONTH (date_time) IN ('4', '5', '6')
AND YEAR (date_time) = ('2012')
GROUP BY suppgroup
)[AS Sub1;]
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 13, 2012 at 5:31 am
Thank you very much, I hate colds they make my head fuzzy (my excuse and I am sticking to it!)
July 13, 2012 at 5:32 am
No worries 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 13, 2012 at 5:53 am
Hmm only one small problem....it doesn't show the column for the suppgroup as well, just the column with the value in it.
Looks like this:
(No column name)
9:01
5:39
4:29
What I needed was a column next to it for each team.
Thanks again...
July 13, 2012 at 6:59 am
Give that a try
Just as a side looks like this query is over complicated a bit..
But if your happy 🙂
SELECT suppgroup, CAST(AvgMins/60 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(AvgMins%60 AS VARCHAR(10)), 2)
FROM
(SELECT SUM(fix_time/60)/COUNT (*) AS AvgMins, suppgroup
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND status <>'17'
AND MONTH (date_time) IN ('4', '5', '6')
AND YEAR (date_time) = ('2012')
GROUP BY suppgroup
)[AS Sub1;]
GROUP BY
suppgroup
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 13, 2012 at 7:02 am
Thanks Andy, will give it a try.
When you say overcomplicated...what do you mean? Always looking to improve , this query is quite old now (I had to dig it out this morning for a new request and I thought I would use this as a base).
July 13, 2012 at 7:23 am
karen.blake (7/13/2012)
Thanks Andy, will give it a try.When you say overcomplicated...what do you mean? Always looking to improve , this query is quite old now (I had to dig it out this morning for a new request and I thought I would use this as a base).
I say this on first glance but with no knowledge of your data and structures or even what you are trying to accomplish.
For example you are (now anyway) using 2 GROUP BY's which may or may not be needed plus the MONTH and YEAR functions on your WHERE
clause will make your query non SARGable (i.e. won't use an index)
As I said this all may be required as I have no idea of your table(s) or data so I may be completely incorrect on this!
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 13, 2012 at 7:30 am
Can you attach the query plan for your original query and this one
Looking at the query and what Andy has already said, the second group by isnt required as the outer query isn't using aggregate functions and if possible best to query the whole date not just the date parts.
SELECT
suppgroup,
CAST(AvgMins/60 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(AvgMins%60 AS VARCHAR(10)), 2)
FROM
(
SELECT
SUM(fix_time/60)/COUNT (*) AS AvgMins,
suppgroup
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND
status <>'17'
AND
date_time >= '2012-04-01'
AND
date_time < '2012-07-01'
GROUP BY
suppgroup
)
July 13, 2012 at 7:39 am
What I basically need to do is pull out the data for the last 3 months on average fix time for all calls but it needs to reflect each team. Although now it actually also needs to be split out each month as well.
Basically this table has many fields but the ones we would be concerned with are suppgroup (this signifies the team), fix_time (this is the fix time in seconds), date_time (this contains the day, month and year information and is the one we query to identify the dates).
All of the fields needed are in my original query, but I can see it does look abit cluttered and perhaps not everything is needed.
Hope this helps if I haven't explained this properly (which is likely) just let me know what I have missed.
July 13, 2012 at 7:45 am
Something like this
SELECT
AVG(fix_time) AS AverageFixTime,
SuppGroup AS SupportGroup,
DATENAME(MONTH,date_time) AS [Month]
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND
status <>'17'
AND
date_time >= '2012-04-01'
AND
date_time < '2012-07-01'
GROUP BY
suppgroup,
DATENAME(MONTH,date_time)
July 13, 2012 at 7:50 am
Thanks Anthony that really helps!
How would I add in converting the time again? Currently it is in seconds and not MM:ss as it was before?
July 13, 2012 at 7:52 am
exactly the same way as before, just replace the above for your original inner query
July 13, 2012 at 8:12 am
Ok I tried this:
SELECT CAST(AvgMins/60 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(AvgMins%60 AS VARCHAR(10)), 2)
FROM
(SELECT
AVG(fix_time/60) AS AverageFixTime,
SuppGroup AS SupportGroup,
DATENAME(MONTH,date_time) AS [Month]
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND
status <>'17'
AND
date_time >= '2012-04-01'
AND
date_time < '2012-07-01'
GROUP BY
suppgroup,
DATENAME(MONTH,date_time)
(I changed one part to fix_time/60 as this means it's then in minutes)
However in the CAST statement it is saying that AvgMins isn't a valid column (which makes sense) but when I change it to AverageFixTime it is still saying the same thing.
I do apologise if I am seeming abit thick, my head feels like cotton wool today!
July 13, 2012 at 8:18 am
Give the below a try.
SELECT
CAST(AverageFixTime/60 AS VARCHAR(10)) + ':' + RIGHT('00' + CAST(AverageFixTime%60 AS VARCHAR(10)), 2),
SupportGroup,
[Month]
FROM
(
SELECT
(AVG(fix_time)/60) AS AverageFixTime,
SuppGroup AS SupportGroup,
DATENAME(MONTH,date_time) AS [Month]
FROM
calltable
WHERE
suppgroup IN ('WEBS', 'Tech','SQL')
AND
status <>'17'
AND
date_time >= '2012-04-01'
AND
date_time < '2012-07-01'
GROUP BY
suppgroup,
DATENAME(MONTH,date_time)
)
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply