Customers with no recent orders?

I thought I could find the answer to this by googling, but had no luck.

How do I show a list of customers who:
(a) Have placed no orders within 14 days,
(b) Have been a customer for at least 14 days. (They do not have to
have placed any orders, ever, to be a customer.)

I'm trying to show inactive customers, but I don't want to include
someone who only joined the customer list recently and has not yet had
time to place any orders.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Customers with no recent orders?

More info. If the tables look like this:

customers
------------------
cust_id
signup_date

orders
------------------
order_id
cust_id
order_date

Then the SQL needs to accomplish something like this:

select * from customers
left join orders on customers.cust_id = orders.cust_id
where signup_date < now() - interval 14 day
and sum(if(order_date > now() - interval 14 day, 1, 0)) = 0

But that gives me "Invalid use of group function".

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Customers with no recent orders?

--_4a77869a-826a-45fe-8ede-25e759d4aa96_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


any column used in any group functions such as SUM needs to be requested in=
select column e.g.
select foo from table where sum(foo)=3D1.00

Martin Gainty=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9
=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
..




> Subject: Re: Customers with no recent orders?
> From: brian@briandunning.com
> Date: Tue=2C 3 Nov 2009 12:54:31 -0800
> To: mysql@lists.mysql.com
>=20
> More info. If the tables look like this:
>=20
> customers
> ------------------
> cust_id
> signup_date
>=20
> orders
> ------------------
> order_id
> cust_id
> order_date
>=20
> Then the SQL needs to accomplish something like this:
>=20
> select * from customers
> left join orders on customers.cust_id =3D orders.cust_id
> where signup_date < now() - interval 14 day
> and sum(if(order_date > now() - interval 14 day=2C 1=2C 0)) =3D 0
>=20
> But that gives me "Invalid use of group function".
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotmail.c=
om
>=20
=20
____________________________________________________________ _____
Find the right PC with Windows 7 and Windows Live.=20
http://www.microsoft.com/Windows/pc-scout/laptop-set-criteri a.aspx?cbid=3Dw=
l&filt=3D200=2C2400=2C10=2C19=2C1=2C3=2C1=2C7=2C50=2C650=2C2 =2C12=2C0=2C100=
0&cat=3D1=2C2=2C3=2C4=2C5=2C6&brands=3D5=2C6=2C7=2C8=2C9=2C1 0=2C11=2C12=2C1=
3=2C14=2C15=2C16&addf=3D4=2C5=2C9&ocid=3DPID24727::T:WLMTAGL :ON:WL:en-US:WW=
L_WIN_evergreen2:112009=

--_4a77869a-826a-45fe-8ede-25e759d4aa96_--

Re: Customers with no recent orders?

Thanks. I added orders.order_date to the list of fields being
selected, but it still returns "invalid use of group function".

On Nov 3, 2009, at 1:09 PM, Martin Gainty wrote:

>
> any column used in any group functions such as SUM needs to be
> requested in select column e.g.
> select foo from table where sum(foo)=1.00

>>
>>
>> select * from customers
>> left join orders on customers.cust_id = orders.cust_id
>> where signup_date < now() - interval 14 day
>> and sum(if(order_date > now() - interval 14 day, 1, 0)) = 0
>>
>> But that gives me "Invalid use of group function".
>>
>> --

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org