Count - Display only rows where count result > 1 match

[rhelle]

Hi,

I'm trying to perform a search using count. The table would look something like this:

Customer   ProductType   ProductVersion   Date
Bob        Pro           7                2005-01-02
Bob        Pro           7                2005-03-30
Bob        Pro           8                2006-01-02
Bob        Std           7                2005-01-02
Bob        Std           8                2006-01-02
Jim        Pro           7                2005-01-02
Jim        Pro           8                2006-02-06

What I'm trying to do, is to list the complete row of all customers that have bought the same ProductType and ProductVersion more than once (date irrelevant).

From the table above, my desired result would be
Bob Pro 7 2005-01-02
Bob Pro 7 2005-03-30
as this is the only two rows where the same customer has bought more than one Pro's of the same version(7).

Any ideas?

 

[Mike Lewis]

You want something like this:

SELECT Customer, ProductType, Version, MAX(Date) ;
  FROM Sales ;
  GROUP BY Customer, ProductType, Version ;
  HAVING COUNT(*) > 1

I haven't tested that, but it should put you on the right track.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Mike, not teste but I am almost sure that the result will be:

Bob        Pro           7                2005-03-30

If I undesratand rhelle right He/She wanto to get:

Bob        Pro           7                2005-01-02
Bob        Pro           7                2005-03-30

If I am wrong sorry.

rhelle,
It is always a good Idea to tell in what version of VFP you working, becuase in VFP 9 you can write this query easily, in smaller version I am not sure, becuase have no istalled here.
But try this:

CREATE CURSOR crsTest (Customer  C(20),  ProductType C(20),  ProductVersion N(1),  Date D)

INSERT INTO crsTest VALUES ([Bob],[Pro],7,{^2005-01-02})
INSERT INTO crsTest VALUES ([Bob],[Pro],7,{^2005-03-30})
INSERT INTO crsTest VALUES ([Bob],[Pro],8,{^2006-01-02})
INSERT INTO crsTest VALUES ([Bob],[Std],7,{^2005-01-02})
INSERT INTO crsTest VALUES ([Bob],[Std],8,{^2006-01-02})
INSERT INTO crsTest VALUES ([Jim],[Pro],7,{^2005-01-02})
INSERT INTO crsTest VALUES ([Jim],[Pro],8,{^2006-02-06})


SELECT crsTest.Customer,  crsTest.ProductType,  crsTest.ProductVersion,  crsTest.Date;
FROM crsTest;
INNER JOIN;
(SELECT Customer,  ProductType,  ProductVersion FROM crsTest GROUP BY Customer, ProductType, ProductVersion ;
   HAVING COUNT(*) > 1) CCC;
ON crsTest.Customer = ccc.Customer AND crsTest.ProductType = ccc.ProductType AND  crsTest.ProductVersion = ccc.ProductVersion;
INTO CURSOR ccc
SELECT ccc

BROW

Borislav Borissov