I struggled through making this query. Now I'm trying to create a new field by multiplying two fields but am not able to make it work.
SELECT Guest.RegistrationID2, Registration.RegistrationID2, StudentID, FirstName, LastName, PreferredName, StudentEmail, MailAddress1, MailAddress2, MailCity, MailState, MailZip, CommunicationPreference, IsActive, ApplicationIDPrimary, Sum(CPSTransaction.AuthorizationAmount) as Total_Paid, COUNT(Guest.GuestRegID) as Guests
FROM Registration Left Join CPSTransaction ON StudentID= ApplicationIDPrimary LEFT JOIN Guest ON Registration.RegistrationID2 = Guest.RegistrationID2
WHERE IsActive = 'True' AND (CommunicationPreference = 'Email')
Group by Guest.GuestRegID, Guest.RegistrationID2, Registration.RegistrationID2, Registration.StudentID, CPSTransaction.AuthorizationAmount, FirstName, LastName, PreferredName, StudentEmail, MailAddress1, MailAddress2, MailCity, MailState, MailZip, CommunicationPreference, IsActive, ApplicationIDPrimary
Order by SUM(AuthorizationAmount), COUNT(Guest.GuestRegID)
When I add this line to the select statement, I break it.
nt - ((55 * COUNT(Guest.GuestRegID) + 100))) as Total_Due
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
I would appreciate any help with this.