• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Specified Expression not included as part of aggregate Function

Hello,

I have a database with Prioperties (PropertyID) and Years.  

I am trying to build a query that will give me the Sum of the RoomsCoorExp + FBExp for each Property.  I've got that part working fine.  I don't want to include any of the RoomsCorrExp or FB Exp if the GrossRevenue = 0 for that particular year.

I tried entering [GrossRevenues]<>0 as the criteria for my expression, and I get the Error "You tried to execute a query that does not include the specified expression 'Not [GrossRevenues]=0' as part of an aggregate function.

The SQL that does work (before I try to add in the GrossRevenue critearia) is:
SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp]+[tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName;

Any thoughts?

Thanks!
cdmac
0
cdmac2
Asked:
cdmac2
  • 2
  • 2
  • 2
1 Solution
 
rockiroadsCommented:
An alternative way, try this
though it might still complain due to +

SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp])+Sum([tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName
0
 
cdmac2Author Commented:
Rockiroads,

Thanks for your response.  The Statment I wrote above DOES work (I tried your statement too, and it also works).  What I can't figure out how to do is get the "GrossRevenues<>0" Criteria in there.

Thx!
0
 
GRayLCommented:
SELECT tblFinancial.PropertyID, tblMASTER.HotelName, Sum([tblFinancial].[RoomsCorrExp])+Sum([tblFinancial].[FBExp]) AS TotalCapEx
FROM tblFinancial INNER JOIN tblMASTER ON tblFinancial.PropertyID = tblMASTER.PropertyID
WHERE <WhichTable?>.GrossRevenues <> 0
GROUP BY tblFinancial.PropertyID, tblMASTER.HotelName;

Add the third line and be sure to replace <WhichTable?> with the correct table name - either tblFinancial or tblMaster
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:
ok, gotcha
I see GRayL has given u the answer, obviously too slow in responding today
I think it has something to do with tree cutters!

0
 
cdmac2Author Commented:
Worked like a charm!

I'm feeling a little off myself.  The solution was very simple.. for some reason I was stuck in the HAVING statemnt.

Thanks guys!


0
 
GRayLCommented:
Thanks, glad I could help.  

rocki, the vision is slowly improving.
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now