Jump to content

Derived Table issue with widget


QEHNick
 Share

Recommended Posts

Good day everyone.

Can anyone please assist with where I am going wrong with this statement?

(SELECT h_fk_team_name, h_ownername, h_fk_servicename,
(SELECT h_ownername FROM h_itsm_requests T2 WHERE T2.h_ownername IN ('names of team', 'more names of team', etc'))
AS 'Third Line' FROM h_itsm_requests T3 WHERE T3.h_fk_servicename= "Out of Hours")

image.png.ce50b6e7718d96bd498f5ec48ec6742e.png

I get the error "MySQL Error 1248: Every derived table must have its own alias" despite my belief that it should work.

Link to comment
Share on other sites

Hi @QEHNick,

I am very unsure what you are attempting to do with either the query and within the context.

Context: by the looks of it you are counting trying to count a tables instead of counting over a field which is contained in table/sub query?.

Query:

1) your query doesn't result in what you are counting over

2) the subquery in your query appears to be used as a field even though it would result in a substantial amount of results. That being said, I doubt it is possible to use a subquery as a field even if the subquery results in a single result.

Might I suggest you contact Care and use some credits for some assistance? Alternatively, there are a few advanced SQL topics covered in : https://www.w3schools.com/sql/

Link to comment
Share on other sites

Thanks @SamS

I'm trying to extrapolate owner names of calls logged under the service "Out of Hours" and count how many were logged.

There are a mixture of disciplines who do out of hours work (and are all in the one team for that duty, their day job is under a different team), I'm separating those team members so we can see which discipline is handling the most calls out of hours.

Link to comment
Share on other sites

I've based the query on one featured on the HB Demo Widget "Average Days Between Major Incidents"

image.png.e0af22111e1102bd2d13b9f57bcdd77a.png

(SELECT h_pk_reference,  h_ownerid, h_datelogged, (SELECT Min(h_datelogged) FROM h_itsm_requests T2 WHERE T2.h_datelogged > T1.h_datelogged AND h_fk_priorityname= 'Major'  AND h_requesttype = 'Incident' ) AS NextDate FROM h_itsm_requests T1 WHERE h_fk_priorityname= 'Major' AND h_requesttype = 'Incident' ) AS AllDays

 

Link to comment
Share on other sites

MIN(...) returns a single value (the smallest) so the statement you're copying converts to something like:

(SELECT h_pk_reference, h_ownerid, h_datelogged, "2022-01-01 00:00:00" AS NextDate FROM h_itsm_requests T1 WHERE h_fk_priorityname= 'Major' AND h_requesttype = 'Incident' ) AS AllDays

Whereas your subquery returns a whole recordset, so simply cannot be converted.

Link to comment
Share on other sites

Hey,

Struggling a bit to understand what you are actually trying to achieve, but using Database Direct might be easier.

SELECT 
  h_ownername as Owner, 
  h_fk_servicename as Service, 
  COUNT(*) as Total 
FROM 
  h_itsm_requests 
WHERE 
  h_fk_servicename = 'Out of Hours' 
  and h_fk_team_name IN ('Team 1', 'Team 2') 
GROUP BY 
  h_ownername 
ORDER BY 
  total DESC

This will display a league table of the number of  "Out of hours" requests assigned to users as part of Team 1 or Team 2.

You can also be a bit more granular and separate out the team assignment too:

SELECT 
  h_ownername as Owner, 
  h_fk_servicename as Service, 
  sum(
    if(
      h_fk_team_name = 'Team 1', 
      1, 0
    )
  ) as "Team 1", 
  sum(
    if(
      h_fk_team_name = 'Team 2', 1, 
      0
    )
  ) as "Team 2", 
  COUNT(*) as Total 
FROM 
  h_itsm_requests 
WHERE 
  h_fk_servicename = 'Out of Hours' 
  and h_fk_team_name IN (
    'Team 1', 'Team 2'
  ) 
GROUP BY 
  h_ownername 
ORDER BY 
  total DESC

it would be helpful if you could provide an example of what you expect the output to look like.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...