SELECT DISTINCT without null value

[sal21]

How to select distinct without null value in filed?

[HansV]

SELECT DISTINCT FieldName
FROM MyTable
WHERE FieldName Is Not Null

[sal21]

SELECT DISTINCT FieldName
FROM MyTable
WHERE FieldName Is Not Null

OK WORK!

Is possible in this combination of null to count RAPPORTO?

Code: Select all

"SELECT DISTINCT RAPPORTO FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL ORDER BY RAPPORTO", cn, adOpenDynamic, adLockOptimistic

[HansV]

Yes, that should work.

[sal21]

Yes, that should work.

Wath you think about?

Code: Select all

"SELECT DISTINCT RAPPORTO, count (*) as TOT_RAPP FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL ORDER BY RAPPORTO", cn, adOpenDynamic, adLockOptimistic

[HansV]

No, that won't work. If you want to add Count, Sum, Max or similar, you must use GROUP BY:


"SELECT RAPPORTO, Count(*) AS TOT_RAPP FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL GROUP BY RAPPORTO"

[sal21]

No, that won't work. If you want to add Count, Sum, Max or similar, you must use GROUP BY:


"SELECT RAPPORTO, Count(*) AS TOT_RAPP FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL GROUP BY RAPPORTO"

ok, tk.

ops! but the count is 1!
I test with:

Code: Select all

Y=rs.Fields(1).Value

perpahs the cursor in my sql string no permit the count?

[sal21]

No, that won't work. If you want to add Count, Sum, Max or similar, you must use GROUP BY:


"SELECT RAPPORTO, Count(*) AS TOT_RAPP FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL GROUP BY RAPPORTO"

Hans sorry but edited my last reply...

[HansV]

Do you loop through the recordset and get 1 for ALL records?

[sal21]

Do you loop through the recordset and get 1 for ALL records?

no... thsi si my test for count and have Y=1:

Code: Select all

....
rs.open "SELECT RAPPORTO, Count(*) AS TOT_RAPP FROM [" & DT & "] WHERE RAPPORTO IS NOT NULL AND NOMINATIVO IS NULL GROUP BY RAPPORTO", cn, adOpenDynamic, adLockOptimistic
Y=rs.Fields(1).Value
....

i think in this acase the record 1 is the value of Count(*) AS TOT_RAPP, or not?

note:
Y is dimensioned as Long

[HansV]

Your line will return the count for the first RAPPORTO in the recordset. That could be 1, while the count is >1 for other records...