MSSQL Select Sum where Sum>X Problem

for aggregate conditions use HAVING, not WHERE.

Try this:

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel, sum(custsls.priceextension) as sales  
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
HAVING SUM(CUSTSLS.PRICEEXTENSION)>=1.23 group by customernum

What about

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel, sum(custsls.priceextension) as sales  
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
WHERE sales>=1.23 group by customernum 

Select all Open in new window

Do you mean you want to be able to use a variable called x?

DECLARE x double

SET x = 1.23

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel, sum(custsls.priceextension) as sales  
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
WHERE SUM(CUSTSLS.PRICEEXTENSION)>= x group by customernum

@IENAXXX:  Invalid column name 'sales'.

@DKROLLCTN:  I was just using X as an example.  Say I wanted to pull data where sum(priceextension)> 450.64.  Your SQL example yielded the following error:  An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

@DANIELWILSON:  Incorrect syntax near the keyword 'group'.

SELECT crm_Customer_Employee_Assignments.CustomerNum, crm_customerPurchaseRollup.custLevel, sum(custsls.priceextension) as sales  
FROM crm_Customer_Employee_Assignments
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assignments.CustomerNum=CUSTSLS.CustNum
LEFT OUTER JOIN crm_customerPurchaseRollup ON crm_Customer_Employee_Assignments.CustomerNum = crm_customerPurchaseRollup.trueCustNum
group by customernum
HAVING SUM(CUSTSLS.PRICEEXTENSION)>= x

CHA-CHING!!!  Thank you!!!

Edit:  Type after user answered question.

Who's Dan?  Is this DKrollCTN?  Is this about the points that were awarded?

DanielWilson posted a suggestion, which I tested, and accepted as solution before I saw the post by DKrollCTN (which is virtually identical to the post by DanielWilson).

I really don't mind sharing points, but the *first* solution was accepted.  If there is an argument about who gets the points, please take it up with management.