asked on MSSQL Select Sum where Sum>X Problem
Will someone please show me how to structure the following SQL statement so that it will work? I do not know how to write it so that it pulls data where the sum of sales is greater than X.
SELECT crm_Customer_Employee_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
WHERE SUM(CUSTSLS.PRICEEXTENSION
SELECT crm_Customer_Employee_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
WHERE SUM(CUSTSLS.PRICEEXTENSION
Microsoft SQL Server 2008
Last Comment
trbbhm
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
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_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
WHERE SUM(CUSTSLS.PRICEEXTENSION
DECLARE x double
SET x = 1.23
SELECT crm_Customer_Employee_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
WHERE SUM(CUSTSLS.PRICEEXTENSION
ASKER
@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.
@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.
ASKER
@DANIELWILSON: Incorrect syntax near the keyword 'group'.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SELECT crm_Customer_Employee_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
group by customernum
HAVING SUM(CUSTSLS.PRICEEXTENSION
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
group by customernum
HAVING SUM(CUSTSLS.PRICEEXTENSION
ASKER
CHA-CHING!!! Thank you!!!
Edit: Type after user answered question.
ASKER
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.
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.
Try this:
SELECT crm_Customer_Employee_Assi
FROM crm_Customer_Employee_Assi
LEFT OUTER JOIN CUSTSLS ON crm_Customer_Employee_Assi
LEFT OUTER JOIN crm_customerPurchaseRollup
HAVING SUM(CUSTSLS.PRICEEXTENSION