SQL Count, HELP!!!

[Labadore]

I have a sales table that has the fields customer_nbr, product_family and Sale_Date. I need to know which customers have been sold atleast 3 different products atleast 3 different times. Then I use an alias Status to get a count of those customers. I may be all wrong because my result is incorrect but heres My sql:


Select Distinct Status, count(status) as ct from
(Select distinct customer_nbr,
CASE
WHEN
Count(distinct product_family) > 2 and Count(distinct sale_date) > 2 Then 'D'
END as Status
From TableName
Group By Customer_nbr
) as qt
group by status


my results should be like STATUS = "D" CT = 1115


my problem is the count on both fields > 2 with the 'and' aren't using the same records.

Thank you in advance for anybodys help!!

 

[ClaireHCM]

Try this

select customer_nbr from sales group by customer_nbr having count(product_family) > = 3 and count(sale_date) > = 3

 

[cmmrfrds]

My first thought is to concatenate the 2 fields and then count. This is untested.

CASE
WHEN
Count(product_family + convert(varchar(10),distinct sale_date,101)) > 2 Then 'D'
END as Status

 

[Labadore]

ClaireHsu that is pretty much the same thing. I would get the same results because there is two counts going on in the 'and' portion of the statement. They are not dealing with the same records.


cmmrfrds that gave me the wrong results aswell.

But thanks to both for giving it a shot.

My issue is with the two counts in one line. what I need is a count on this criteria:

Where the customer has purchased a distinct product(prod_family) three or more times (Sale_Date) in three or more different distinct product Families(distinct Prod_Family > 2).

Nutshell: 3 or more products sold in 3 or more product families


I know this one is pretty tough. I can't figure it out. ANY HELP WOULD BE GREATLY APPRECIATED!!!

 

[ClaireHCM]

Try this ,it doesnt look good...but it will give u the result I believe!

select customer_nbr from
(select left(stage,charindex(' ',stage)) as customer_nbr from
(select customer_nbr+' '+product_family as stage from sales )a
group by stage having count(stage) > = 3) b group by customer_nbr having count(customer_nbr) >= 3