SQL Date Range and Totaling

Question

0.00/5 (No votes)

See more:

Hey I have a problem I'm trying to do a select as seen below:

SQL

SELECT SUM(Invoice.TotalPrice) AS "Total", Customer.LastName, Customer.FirstName
FROM Invoice
INNER JOIN Customer
ON Invoice.CustomerID = Customer.CustomerID
WHERE DATEDIFF ( MONTH , Invoice.DateOrderPlaced , GETDATE() ) > 3 AND "Total"  > 20000
GROUP BY Invoice.CustomerID, Customer.LastName, Customer.FirstName;

The problem i've got is the:

3 AND "Total"  > 20000

I want it to mean the total of price where customer ID is the same. Anyone got any suggestions?

Posted 14-Dec-11 23:49pm

WurmInfinity

Updated 15-Dec-11 0:43am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dylan Morley · Accepted Answer · 2011-12-15T00:52:00

Solution 1

Just use the summary function in a HAVING clause

SQL

SELECT SUM(Invoice.TotalPrice) AS "Total", Customer.LastName, Customer.FirstName
FROM Invoice
INNER JOIN Customer
ON Invoice.CustomerID = Customer.CustomerID
WHERE DATEDIFF ( MONTH , Invoice.DateOrderPlaced , GETDATE() ) > 3
GROUP BY Invoice.CustomerID, Customer.LastName, Customer.FirstName
HAVING SUM(Invoice.TotalPrice) > 20000

Posted 15-Dec-11 0:52am

Dylan Morley

Updated 15-Dec-11 0:53am

v2

Comments

WurmInfinity 15-Dec-11 6:54am

Thank you kindly.

Sara Dark 15-Dec-11 7:59am

thank you your answer was enough i know the difference now :)

Sara Dark 15-Dec-11 7:53am

please can u explain briefly y "WHERE DATEDIFF ( MONTH , Invoice.DateOrderPlaced , GETDATE() ) > 3" wasn't added in the query with having
HAVING (SUM(Invoice.TotalPrice) > 20000 AND DATEDIFF ( MONTH , Invoice.DateOrderPlaced , GETDATE() ) > 3).
Im no expert i just want to know what difference does it make?

Dylan Morley 15-Dec-11 7:57am

Have a look at this article which explains a few things...

http://www.codeproject.com/KB/database/Where_Vs_Having_Clause.aspx

"Here's the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause. "

In the above query, the DATEDIFF statement was saying which records we wanted in the results but no aggregation took place, so we can use the WHERE clause for this.

However, tTotalPrice requires aggregation, so it has to happen in the HAVING clause.