Solved

MSSQL Select Sum where Sum>X Problem

Posted on 2012-12-20
10
391 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
GRANT, REVOKE, DENY 4 36
Sql query 34 35
BULK LOGGED - log full 9 25
Authentication error 1 38
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now