List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:January 5 2006 3:54pm
Subject:Re: SELECT help.
View as plain text  
Richard Reina wrote:
> Can someone help me write a query to tell me the customer numbers (C_NO) of
> those who've had more than 4 transactions but none in the last 6 months?
>   
>  transactions_table
>   | ID   | C_NO |    DATE      | AMOUT |
>   | 2901 |  387 | "2003-10-09" | 23.00 |
>    
>   Obviously my table has many more entries.
>   
>   Thank you for any help.
>   
>   Sincerely,
>   
>   Richard Reina
>    
> A people that values its privileges above its principles soon loses both.
>  -Dwight D. Eisenhower.

Something like:

   SELECT C_NO FROM transactions_table
   GROUP BY C_NO
   HAVING COUNT(*) >= 4
      AND COUNT(DATE > CURDATE() - INTERVAL 6 MONTH) = 0;

Michael
Thread
SELECT help.Richard Reina5 Jan
  • Re: SELECT help.Rhino5 Jan
    • Re: SELECT help.Richard Reina5 Jan
    • Re: SELECT help.SGreen5 Jan
      • Re: SELECT help.Richard Reina7 Jan
  • Re: SELECT help.Michael Stassen5 Jan
  • Re: SELECT help.James Harvard5 Jan
  • Re: SELECT help.Peter Brawley5 Jan