Solved

MSSQL Select Sum where Sum>X Problem

Posted on 2012-12-20
10
393 Views
Last Modified: 2012-12-20
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_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)>=1.23 group by customernum
0
Comment
Question by:trbbhm
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 38709790
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
0
 
LVL 10

Expert Comment

by:ienaxxx
ID: 38709791
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 

Open in new window

0
 
LVL 11

Expert Comment

by:David Kroll
ID: 38709793
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:trbbhm
ID: 38709812
@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.
0
 

Author Comment

by:trbbhm
ID: 38709824
@DANIELWILSON:  Incorrect syntax near the keyword 'group'.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 38709831
Adjusting mine a little ...

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 ,crm_customerPurchaseRollup.custLevel
HAVING SUM(CUSTSLS.PRICEEXTENSION)>=1.23
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 38709838
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
0
 

Author Closing Comment

by:trbbhm
ID: 38709843
CHA-CHING!!!  Thank you!!!
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38709895
Edit:  Type after user answered question.
0
 

Author Comment

by:trbbhm
ID: 38709922
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.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question