V4MD aggregation in subquery

[info at vallemediatime.com]

----- Original Message ----- 
From: "Ruslan Zasukhin" <sunshine at public.kherson.ua>
To: <valentina-beta at lists.macserve.net>
Sent: Sunday, December 04, 2005 10:05 AM
Subject: Re: V4MD aggregation in subquery


> 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

Thanks Ruslan,
I've tried it and at the end I can use this working query:

SELECT Course_Name, COUNT(Reg_Code) \
FROM Courses JOIN Registration ON Course_ID = Reg_Course_Ref \
WHERE Reg_Code = 1 \
GROUP BY Course_Name

But if I want add a ner field into first line selected:
eg
SELECT Course_ID, Course_Name, COUNT(Reg_Code) \ ....

the cursor become VOID

some suggestion?

Thanks Paolo




>
>> 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]
>
>
> _______________________________________________
> Valentina-beta mailing list
> Valentina-beta at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina-beta
>