V4MD aggregation in subquery

[Ruslan Zasukhin]

On 12/4/05 10:47 AM, "info at vallemediatime.com" <info at vallemediatime.com>
wrote:

Hi Paolo,

> Hi all,
> I need to get a record set like this example by SQL way, I read that it is
> necessary a subquery... right?
> 
> T1   Registration  (Reg)
> Fld  Reg_Code  |  Reg_Course_Ref  |  Reg_Student_Ref
> R1      1                 1000                       10
> R2      1                 1000                       11
> R3      2                 1001                       12
> 
> T2   Courses
> Fld  Course_ID  |  Course_Name |
> R1   1000                Math
> R2   1001                Music
 
>  I want to get a count of all courses that have a Reg_Code = 1
> eg
> Fld   Course_Name | Reg_Code_Count
> R1      Math                      2
> R2      Music                    1

I recommend to use link functions.

Select CourseName, link_count( Reg_Course_Ref, Courses, Registration )
FROM Courses
WHERE link_count( Reg_Course_Ref, Courses, Registration ) = 1


--------
In standard way can be as:

SELECT Course_Name, COUNT(*) as 'f2'
FROM Courses join Registration ON Corsi_ID = Reg_Corsi_Ref
GROUP BY Course_Name
HAVING f2 = 1


> My wrong tentative is this:
> query = "SELECT Course_Name, \
> (SELECT COUNT(Reg_Code) \
> FROM Registration  WHERE Reg_Code = 1) \
> FROM Corsi \
> WHERE Corsi_ID = Reg_Corsi_Ref  "
> 
> Can somebody help me?
> Thanks

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]