Select from my results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select from my results

Author

Topic

rcp
Starting Member

32 Posts

Posted - 2012-02-06 : 12:37:34

Hi,

I am trying to select part of my results as my over all result. Below is the query so far.

WITH
T1Counts
AS
(
select
Convert(date,created_date) AS ordDate
,customer_id
from custorder
where YEAR(created_date) = '2011'
and status like 'paid'
)
SELECT COUNT(T1.customer_id) AS [Loyal]
,T1.customer_id
FROM T1Counts T1

INNER Join (select distinct customer_id
from customer_order
where MONTH(created_date) = '1'
and YEAR(created_date) = '2012'
and status like 'paid'
) AS b
ON T1.customer_id=b.customer_id

GROUP BY T1.customer_id

This gives me a list of customers thathave placed an order and how many orders they have place.eg.

Loyal customer_id
7 F950D370-6554-11D4-AB7E-009027C2E644
1 46C17FC7-980D-11D4-AB80-009027C2E644
3 0D9216FA-7440-11D4-AB80-009027C2E644

What I would like to do is select the ones that have more than x orders. So more than 6 would just give me

Loyal customer_id
7 F950D370-6554-11D4-AB7E-009027C2E644

or more than 2 would give me

Loyal customer_id
7 F950D370-6554-11D4-AB7E-009027C2E644
3 0D9216FA-7440-11D4-AB80-009027C2E644

Any help would be much appreciated.

Regards,

Roland

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-06 : 12:48:54

Add a HAVING clause at the very end - for example:


....
GROUP BY T1.customer_id

HAVING COUNT(T1.customer_id) >= 7

rcp
Starting Member

32 Posts

Posted - 2012-02-06 : 12:50:40

That has worked perfectly, thank you for your help.

Roland

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources