Derived Table issue with widget

[QEHNick]

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")

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

[SamS]

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/

[QEHNick]

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.

[QEHNick]

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

(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

 

[QEHNick]

I've logged it with Care. When I have a result, I'll post it here for any other eager beavers.

[Steve Giller]

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.

[Met]

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.