Link to home
Start Free TrialLog in
Avatar of 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!!!

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Hi steverguy,

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

SELECT s2.CustomerRefListID, Count(*)
    (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


Avatar of mcallarse
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of steverguy


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!