selecting distinct values for one specific field

[skorda]

I have a rather complex query that is grouping counts. There is one specific field where I need to count distinct values only (but for specific criteria). I am using VFP 9 Beta and trying to use a sub-query in the field list like this (there are a lot more SUM's but I removed them for clarification):

SELECT County, State, SUM(IIF(LEN(ALLTRIM(fax)) = 10 AND ;
ISDIGIT(fax) AND RSPFlag AND DNC = 0,1,0)) as FaxResp, ;
[B]
(SELECT COUNT(distinct email) FROM csrProspects ;
WHERE '@' $ email AND !RSPFlag AND DNE = 0 ;
GROUP BY County, State) as EmailCount, ;
(SELECT COUNT(distinct email) FROM csrProspects ;
WHERE '@' $ email AND RSPFlag AND DNE = 0 ;
GROUP BY County, State) as EmailResp, ;
[/B]
FROM csrProspects GROUP BY County, State ;
ORDER BY State, County INTO CURSOR csrCounts

But I get the error: Subquery returned more than one record (Error 1860). I'm probably not using the right syntax. Has anyone done this before? Or if anybody has any ideas about how to do this in VFP 6 that would be great too!

Sebastian

 

[ramani]

HI

your code cannot be followed by me.
Once you use DISTINCT only one record will be picked up. Then what is the point tpo count().

If you want the count, then drop the distinct words.

Instead use GROUP BY syntax.
Probably this could help... (use this code as guidance.. need not be the same)

SELECT ;
SUM(IIF(RSPFlag,0,1)) AS EmailCount, ;
SUM(IIF(!RSPFlag,1,0)) AS EmailResp ;
FROM csrProspects ;
WHERE '@' $ email AND DNE = 0 ;
GROUP BY County, State ;
INTO cursor myCursor

I assumed that duplicate 'emails' are not there. Otherwise you have to modify. Also I dont understand.. GROUP BY Country, State or whatever your requirement.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Mike Lewis]

Sebastian,

I can see why this error occurred, although I'm not sure what the solution is.

When you write a sub-query in the expression list (that is, in the list that immediately follows the SELECT keyword), that sub-query must generate exactly one row and one column. Think of it as an expression that returns a value.

Your two sub-queries include a GROUP BY, so will return a row for each member of the group. In other words, there will be multiple rows in the sub-query result set, which the outer SELECT cannot deal with.

You can verify that by removing the GROUP BY clauses in the two sub-queries. That will get rid of the error message, but of course it won't give you the result you want.

I would guess that the best way to solve the problem would be to generate the counts in a couple of intermediate cursors, and joint these to the main query. But no doubt someone will come up with a better solution.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[skorda]

Mike, I have seen Group By used in the field list before (at least in MS SQL), however not in this way specifically. I am trying to get a count for each row (that is, for each county). I was hoping to avoid having to do these counts in seperate selects, but that may be my only choice, seeing as how nobody seems to have any other ideas.

Ramani, the code you provided won't work for me because I need to get a count of unique email addresses for each county. Doing a count(distinct [field]) does count the unique values in the field. However, I have additional criteria that does not apply to the rest of the query.

Thank you both for your input.

Sebastian

 

[Mike Lewis]

Sebastian,

I have seen Group By used in the field list before

The problem is not the presence of GROUP BY in a sub-select. It's the fact that the sub-select is returning more than one row. If the sub-select was modified, say, to return TOP 1, the problem wouldn't occur, even though the GROUP BY was still there.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com