Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MSSQL Select Sum where Sum>X Problem

Posted on 2012-12-20
10
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 2000 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 41

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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