Helpdesk Report with htblnotes - note ntext

[Null]

I have a Helpdesk report that shows which tickets were closed in the last X days. I have it sorted by user department.
The report works great as is below.

Select Top 1000000 htblusers.department As Department,
htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
htblticket.subject As Subject,
Max(htblhistory.date) As Closure
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticketstates htblticketstates1
On htblticketstates1.ticketstateid = htblhistory.ticketstateid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
htblhistorytypes.name In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates1.statename =
'Closed'
Group By htblusers.department,
htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.date,
htblticket.updated,
htbltickettypes.typename,
htblticketstates.statename,
htblpriorities.name,
htblsource.name,
htblusers.name,
htblusers1.name,
htblusers2.name,
htblticket.subject
Having Max(htblhistory.date) > GetDate() - 7
Order By Closure Desc,
htblticket.ticketid

What I want to do is add another column called "Ticket Text" that shows the original notes in the ticket. For example, when a user emails in a problem the notes will say, for example, "Issues printing to 7th floor color printer". This would allow anyone looking at the report to get a quick glimpse at the "meat and potatoes" of the ticket.

HOWEVER, when I add the htblnotes table, select "Note ntext" and set the alias as "Ticket text" and click "Save & Run" I get the error:

: Column 'htblnotes.note' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is how the report for that looks.

Select Top 1000000 htblusers.department As Department,
htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
htblticket.subject As Subject,
Max(htblhistory.date) As Closure,
htblnotes.note As [Ticket text]
From htblticket
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htbltickettypes On htblticket.tickettypeid =
htbltickettypes.tickettypeid
Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
htblusers2.userid
Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
Inner Join htblticketstates htblticketstates1
On htblticketstates1.ticketstateid = htblhistory.ticketstateid
Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid And
htblusers.userid = htblnotes.userid And htblusers1.userid = htblnotes.userid
And htblusers2.userid = htblnotes.userid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
htblhistorytypes.name In ('Status changed', 'Note added and state changed',
'Internal note added and state changed') And htblticketstates1.statename =
'Closed'
Group By htblusers.department,
htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar),
htblticket.date,
htblticket.updated,
htbltickettypes.typename,
htblticketstates.statename,
htblpriorities.name,
htblsource.name,
htblusers.name,
htblusers1.name,
htblusers2.name,
htblticket.subject
Having Max(htblhistory.date) > GetDate() - 7
Order By Closure Desc,
htblticket.ticketid

Any recommendations?

[Esben_D]

You'll have to group all the column in your select if you use any kind of aggregation. However, ntext acts up a lot of the time with these kind of things. Best thing you can do in convert the ntext to nvarchar. Like i did below:

Select Top 1000000 htblusers.department As Department,
  htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date As CreationDate,
  htblticket.updated As LastUpdated,
  htbltickettypes.typename As Type,
  htblticketstates.statename As State,
  htblpriorities.name As Priority,
  htblsource.name As Source,
  htblusers.name As [User],
  htblusers1.name As AssignedAgent,
  htblusers2.name As UserLastNote,
  htblticket.subject As Subject,
  Max(htblhistory.date) As Closure,
  Convert(nvarchar(max),htblnotes.note) [Ticket text]
From htblticket
  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
  Inner Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htblusers On htblusers.userid = htblticket.fromuserid
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
  Inner Join htbltickettypes On htblticket.tickettypeid =
    htbltickettypes.tickettypeid
  Inner Join htblsource On htblticket.sourceid = htblsource.sourceid
  Inner Join htblusers htblusers2 On htblticket.userid_lastnote =
    htblusers2.userid
  Inner Join htblhistory On htblticket.ticketid = htblhistory.ticketid
  Inner Join htblhistorytypes On htblhistorytypes.typeid = htblhistory.typeid
  Inner Join htblticketstates htblticketstates1 On
    htblticketstates1.ticketstateid = htblhistory.ticketstateid
  Inner Join htblnotes On htblticket.ticketid = htblnotes.ticketid And
    htblusers.userid = htblnotes.userid And htblusers1.userid = htblnotes.userid
    And htblusers2.userid = htblnotes.userid
Where htblticketstates.statename = 'Closed' And htblticket.spam <> 'True' And
  htblhistorytypes.name In ('Status changed', 'Note added and state changed',
  'Internal note added and state changed') And htblticketstates1.statename =
  'Closed'
Group By htblusers.department,
  htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar),
  htblticket.date,
  htblticket.updated,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblpriorities.name,
  htblsource.name,
  htblusers.name,
  htblusers1.name,
  htblusers2.name,
  htblticket.subject,
  Convert(nvarchar(max),htblnotes.note)
Having Max(htblhistory.date) > GetDate() - 7
Order By Closure Desc,
  htblticket.ticketid