error with sql command

[lucaazori]

hi, here is my data base structure:

i try an sql command that return for each examen its number of tests, so i tried:

Dim sqlQRY1 As String = "SELECT Int_Exa,count(*) as 'TOTAL' from TEST T1,TEST T2 where T1.Int_Exa=T2.Int_Exa"

that`s did not work, so i tried to do that:

Dim sqlQRY1 As String = "SELECT EXAMEN.Int_Exa,count(Id_Test) from EXAMEN,TEST where EXAMEN.Int_Exa=TEST.Int_Exa"

these two gives me error, i hope you help me to find the appropriate sql command ;)

[lucaazori]

i tried this:

SELECT EXAMEN.Int_Exa,count(Id_Test) as nbrTest FROM EXAMEN,TEST GROUP BY EXAMEN.Int_Exa

it worked but don`t give me what i looking for :(

[G_Waddell]

Try:

SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa

The result set should will list each exam in the TEST table with a count of the number of each.

If you want a count of ALL exam (not just the ones that have tests,) then try:

SELECT EXAMIN.Int_Exa, ISNULL(T.nbrTest, 0) AS nbrTest FROM EXAMIN LEFT JOIN (
SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa) AS T ON EXAMIN.Int_Exa = T.Int_Exa ORDER BY EXAMIN.Int_Exa

This will take each Int_Exa in the Examin table and left Join it to the Resultset from the previous example. If there is no match (i.e. no record in the TEST table,) it would return Null but the ISNULL function allows us to substitute the value 0.

[lucaazori]

Hi, thx for your suggestion. I did this:

SELECT EXAMEN.Int_Exa, ISNULL(T.nbrTest, 0) AS nbrTest FROM EXAMEN LEFT JOIN (SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa) AS T ON EXAMEN.Int_Exa = T.Int_Exa ORDER BY EXAMEN.Int_Exa

it gives error:

wrong number of arguments used with function in query expression 'ISNULL(T.nbrTest,0'.

[lolafuertes]

If you are using ACCESS, then you can change a little bit the SQL sentence as:

SELECT EXAMEN.Int_Exa, IIF(ISNULL(T.nbrTest), 0, T.nbrTest) AS nbrTest FROM EXAMEN LEFT JOIN (SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa) AS T ON EXAMEN.Int_Exa = T.Int_Exa ORDER BY EXAMEN.Int_ExaSELECT EXAMEN.Int_Exa, ISNULL(T.nbrTest, 0) AS nbrTest FROM EXAMEN LEFT JOIN (SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa) AS T ON EXAMEN.Int_Exa = T.Int_Exa ORDER BY EXAMEN.Int_Exa

Hope this helps

[lucaazori]

thank you, i tried this :

SELECT EXAMEN.Int_Exa, IIF(ISNULL(T.nbrTest), 0, T.nbrTest) AS nbrTest FROM EXAMEN LEFT JOIN (SELECT int_Exa, COUNT(*) as nbrTest FROM TEST GROUP BY int_Exa) AS T ON EXAMEN.Int_Exa = T.Int_Exa ORDER BY EXAMEN.Int_Exa

it worked fine, thank you again for help ;)