Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Russell Hill
  Where is Russell Hill?
 Sydney
 Australia
 Russell Hill
 Tags
Subject: SQL SELECT
Thread ID: 143350 Message ID: 143350 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: General VFP Topics
Date: Tuesday, August 28, 2007 6:51:10 AM         
   


Hi all,

I am trying to use a conditional count within SQL.

I would like a count of all records by a field type (OOM) as follows:

SELECT OOM, COUNT(*) ;
  FROM MyTable ;
 GROUP BY OOM


This produces something like the following:

OOM	COUNT
  1	 1789
  2	 2132
  3	 1456
  4  	 1678


In addition, I would like a conditional count within each OOM group. Specifically, I need a count where a given field has a single value (eg. VALUE = 1). If there are 453 records where OOM = 1 and VALUE = 1 then I require:

OOM	COUNT	CONDCOUNT
  1	 1789	      453
  2	 2132	      ...
  3	 1456	      ...
  4	 1678	      ...


I am struggling to get the right syntax.

Can anyone help with this?

Thanks,

Russell.

ENTIRE THREAD

SQL SELECT Posted by Russell Hill @ 8/28/2007 6:51:10 AM
RE: SQL SELECT Posted by Vladimir Zhuravlev @ 8/28/2007 6:54:46 AM
RE: SQL SELECT Posted by Russell Hill @ 8/28/2007 7:26:58 AM