From: Peter Brawley Date: January 5 2006 6:52pm Subject: Re: SELECT help. List-Archive: http://lists.mysql.com/mysql/193554 Message-Id: <43BD6AF6.7050906@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-43BD6AF76581=======" --=======AVGMAIL-43BD6AF76581======= Content-Type: multipart/alternative; boundary=------------070501000702060500080504 --------------070501000702060500080504 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Richard, >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? Something like this? SELECT c_no, COUNT(c_no) AS cnt FROM transactions_table WHERE NOT EXISTS ( SELECT c_no FROM transaction_table WHERE DATEDIFF(NOW(),date)<=182 ) GROUP BY c_no HAVING cnt > 4; PB ----- 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. > > >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Free Edition. >Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 > > --------------070501000702060500080504 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Richard,

>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?

Something like this?

  SELECT
    c_no,
    COUNT(c_no) AS cnt
  FROM transactions_table
  WHERE NOT EXISTS (
    SELECT c_no
    FROM transaction_table
    WHERE DATEDIFF(NOW(),date)<=182
  )
  GROUP BY c_no
  HAVING cnt > 4;

PB

-----

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.
  

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006
--------------070501000702060500080504-- --=======AVGMAIL-43BD6AF76581======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 1/4/2006 --=======AVGMAIL-43BD6AF76581=======--