Click here to Skip to main content
15,922,315 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
So i've a made a database for a fishing company. I need to know how I would select customers who have purchased over £20,000 worth of fish over three months, six months, nine months and annually. I want to use only the payment table I have which has in it: Payment ID, CustomerID, CardNumber, Date_Begin, Date_End and Fish Price.
Posted

1 solution

SQL
SELECT
CUSTOMERS.ID
FROM CUSTOMERS
WHERE (SELECT SUM (PAYMENT .FISH_PRICE) FROM PAYMENT  WHERE PAYMENT.CUSTOMER_ID = CUSTOMERS.ID AND PAYMENT.DATE_BEGIN >= : DATE_BEGIN AND PAYMENT.DATE_END <= :DATE_END) > '20000'


Untested :)

Uuuups sorry only payment table

SQL
SELECT
PAYMENT.CUSTOMER_ID,
SUM(PAYMENT.FISH_PRICE)
FROM PAYMENT
WHERE PAYMENT.DATE_BEGIN >= :DATE_BEGIN AND PAYMENT.DATE_END <= :DATE_END
GROUP BY PAYMENT.CUSTOMER_ID
HAVING
SUM(PAYMENT.FISH_PRICE) > '20000'


Still untested :)
 
Share this answer
 
v6
Comments
programmer1234 11-Dec-11 14:39pm    
That works, thank you.
[no name] 12-Dec-11 18:46pm    
You are welcome. Sorry for the first statement, this does not have a lot to do with your question. Seems that I was somewhat absence while writing the answer.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900