Thank you very much to all who responded. I ended up using Shawn's solution,
the others seem good as well.
Thanks again. Have a great weekend.
Richard
[EMAIL PROTECTED] wrote:
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: <[email protected]>
> 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]
>
A people that values its privileges above its principles soon loses both.
-Dwight D. Eisenhower.
