Try this: SELECT c_no , SUM(1) as total_tx , SUM(if(`date` >= now() - interval 6 month,1,0)) as recent_tx FROM transactions_table GROUP BY c_no HAVING total_tx > 4 and recent_tx = 0;
Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Rhino" <[EMAIL PROTECTED]> wrote on 01/05/2006 10:43:15 AM: > > ----- Original Message ----- > From: "Richard Reina" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Thursday, January 05, 2006 10:29 AM > Subject: SELECT help. > > > > 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. > > > What version of MySQL are you using? I would give a different answer if you > had a version that supported subqueries than if you were using an older > version that didn't support them. > > Rhino > > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.371 / Virus Database: 267.14.13/221 - Release Date: 04/01/2006 > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >