Need help with SQL for reporting on total tickets closed by owner per month

Question

I'm still brand new at SQL and learning the Kbox schema. It's not clear if HD_Ticket.Owner_ID can be cross referenced to User.User ID and if so, then I'm not sure how to display the Full_Name of the user with ownership. I keep getting an 'unknown column User.Full_Name in field list' in my Select statement. This is what I have so far:

Select count(*) as TotalTicketCount, Monthname(TIME_CLOSED) as Month, User.FULL_NAME as Owner
From HD_TICKET

Left Join USER as U
    on HD_Ticket.OWNER_ID=U.USER_ID
where HD_QUEUE_ID = 'Help Desk'
        and Year(TIME_CLOSED) = '2014'
        and Owner in ('name1', 'name2', etc)
Group by Owner, Monthname(TIME_CLOSED);

Is their a better way to request a list of owners?

Thanks for any and all help.

Answer

You can actually create a report using the wizard that will give you SQL as below

SELECT O.USER_NAME AS OWNER_USER_NAME,

HD_TICKET.ID,

HD_TICKET.TIME_CLOSED,

HD_TICKET.TITLE

FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)

WHERE (HD_TICKET.HD_QUEUE_ID = 3) AND ((HD_STATUS.NAME = 'Closed')) ORDER BY OWNER_USER_NAME

You can see that the Owner_ID shown in the ticket does match the USER>ID value and as such to get the name you need to join the tables on the ID field

Answer

You are getting the error because when you created the join to the user table you aliased it as U. Therefore, when you want to select columns from the table you need to use U.FULL_NAME and not USER.FULL_NAME. I would suggest that when you are building your joins you should use a descriptive alias:

JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

If you do this then your select statement looks like OWNER.FULL_NAME which make it clear what you are selecting. If you build more complicated reports that include multiple users, i.e. owner, submitter and approver, this will help keep things clean.