Hi,

I have a requirement for a query to select and group results column wise, of the number of registered members per registration category per qualification. A one-to-many table named Qualif_Register points the main table named Register.

The SQL query will look something like this:

Select
Qualif_Name,
Categories_Name,
count(case when RegDate Between '01.01.1982' and '31.12.2011' then 1 end) as CumTotal_1982_2011,
count(case when RegDate Between '01.01.1982' and '31.12.2012' then 1 end) as CumTotal_1982_2012
From V_Register_Qualif
where RegStatus_ID = 1
Group by
Qualif_Name,
Categories_Name

In above mentioned SQL statement, I count and group the number of qualifications per category. Actually I want to count the number of people with the various qualifications per category.

The following SQL statement will give the correct answer for the number of professional Scientists with a B Sc degree:

Select Count(distinct(Qualif_Name || Categories_Name || Register_ID)) as Qualif_Categories_Name_Count
From V_Register_Qualif
where RegDate Between '01.01.1982' and '31.12.2012'
and Qualif_ID = 20
and Categories_ID = 4
and RegStatus_ID = 1

V_Register_Qualif is a view on the one-to-many table Register_Qualif pointing to the main table Register.

Any idea how I have to adjust my select ... group by statement to count the number of people and not the number of qualifications?

I suspect the solution lies in the use of "distinct(Qualif_Name || Categories_Name || Register_ID)" but I simply can't figure it out.


Regards,

Nols Smit