This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.
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
Asked:
-
4
2
2- +2
1 Solution
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
@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.
Adjusting mine a little ...
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 ,crm_customerPurchaseRollu
HAVING 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
group by customernum ,crm_customerPurchaseRollu
HAVING 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
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
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.
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 Already a member? Login.
Featured Post
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
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