You tried to execute a query that does not include the specific expression as part of an aggregate function?????????

I am using the following cross-tab query but an error message comes out "You tried to execute a query that does not include the specific expression'TimeCode' as part of an aggregate function" any suggestions ? thanks !

TRANSFORM tbPlacement.TimeCode
SELECT tbStudentInfo.StudentName
FROM tbStudentInfo INNER JOIN (tbDetailsOfDates INNER JOIN (tbDetailsOfWeekday INNER JOIN (tbPlacement INNER JOIN tbStudentGroupList ON tbPlacement.GroupName = tbStudentGroupList.GroupName) ON tbDetailsOfWeekday.DayNo = tbPlacement.DayNo) ON (tbDetailsOfWeekday.DayNo = tbDetailsOfDates.DayNo) AND (tbDetailsOfDates.DayNo = tbPlacement.DayNo) AND (tbDetailsOfDates.WeekNo = tbPlacement.WeekNo) AND (tbDetailsOfDates.TermNo = tbPlacement.TermNo)) ON tbStudentInfo.StudentID = tbStudentGroupList.StudentID
WHERE (((tbPlacement.Venue)="4d") AND ((tbPlacement.VenueCode)="sh"))
GROUP BY tbStudentInfo.StudentName
ORDER BY tbStudentInfo.StudentName, tbDetailsOfDates.Dates
PIVOT tbDetailsOfDates.Dates;
mmccyAsked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
By way of explanation, in a crosstab query, if the field in the TRANSFORM statement is not part of an Aggregate function, it must be named in the GROUP BY clause.  Normally, that field is wrapped in an aggregate function such as Sum, First, Last, Min, Max, etc. so the crosstab can serve as a collector of data, with rows as per the SELECT statement and additional columns as per the PIVOT clause.  It is hard to imagine a meaningful crosstab query without an aggregate function in the TRANSFORM statement.  When the TRANSFORM statement is an aggregate function, it is not part of the GROUP BY clause.  
0
 
rockiroadsCommented:
Does the TRANSFORM statement not require an aggregate function?

e,g,

TRANSFORM Sum(tbPlacement.TimeCode

or

TRANSFORM Var(tbPlacement.TimeCode
0
 
mmccyAuthor Commented:
there is no aggregate function for the transform statement
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
rockiroadsCommented:
What Im saying is, dont u need one
if u add one in, do u still get the error
0
 
rockiroadsCommented:
Perhaps thats how I needed to phrase it, as that was the point I was trying to make

mmccy, in your question after this, u used FIRST, just wondering whether what I wrote actually helped or not - feedback purposes
0
 
GRayLCommented:
Thanks, glad I could help.
0
 
GRayLCommented:
Thanks, glad I could help.
0
 
rockiroadsCommented:
GRayL appreciated it so much, it stressed it again :)
0
 
rockiroadsCommented:
urm, typo
should be

he stressed

not

it stressed

ooops
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.