List:General Discussion« Previous MessageNext Message »
From:Richard Reina Date:January 7 2006 2:36am
Subject:Re: SELECT help.
View as plain text  
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

SGreen@stripped 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" <rhino1@stripped> wrote on 01/05/2006 10:43:15 AM:

> 
> ----- Original Message ----- 
> From: "Richard Reina" <richard@stripped>
> To: <mysql@stripped>
> 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/mysql?unsub=1
> 
  



A people that values its privileges above its principles soon loses both.
 -Dwight D. Eisenhower.
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