steverguy

asked on

# SQL Sum Question

I'm connecting to a QuickBooks database using ODBC and MS Access. I'm trying to use SQL to pull data in a way quickbooks will not allow. For example, I would like to get a list of people who in the past year have ordered 3 or more times with a value of over $x.

There are several feilds in the Sales Table, which seams to be the only table I'll need to get this information. The names of the fields are:

1. ShipDate (determining when ordered)

2. Subtotal (determining what amount)

3. CustomerRefListID (the Customer's ID)

I'm kind of lost as to where to even begin.

Select *, Sum(CustomerRefListID) as CustTotal from Sales WHERE CustTotal > 3...

Of course I know this won't work, since CustTotal can't be determined like that... Even if it could be I'm not sure I'm approaching it correctly. Any advice, guidence or suggestions would be appreciated!!!

Thanks!

There are several feilds in the Sales Table, which seams to be the only table I'll need to get this information. The names of the fields are:

1. ShipDate (determining when ordered)

2. Subtotal (determining what amount)

3. CustomerRefListID (the Customer's ID)

I'm kind of lost as to where to even begin.

Select *, Sum(CustomerRefListID) as CustTotal from Sales WHERE CustTotal > 3...

Of course I know this won't work, since CustTotal can't be determined like that... Even if it could be I'm not sure I'm approaching it correctly. Any advice, guidence or suggestions would be appreciated!!!

Thanks!

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks for your help, mcallarse! Trying to find SQL Tutorials that cover things like this is tough. I tried to give you partial credit Patrick, but I don't think I did it right. Sorry...

Thanks again for the help!

Thanks again for the help!

There is probably a more elegant way to do this, but this should work:

SELECT s2.CustomerRefListID, Count(*)

FROM

(SELECT s.CustomerRefListID, s.ShipDate, s.Subtotal

FROM Sales AS s

WHERE (s.ShipDate BETWEEN #9/1/2005# AND #8/31/2006#) AND s.Subtotal >= 50) AS s2

GROUP BY s2.CustomerRefListID

HAVING Count(*) >= 3

Regards,

Patrick