Group by and return sigle value of counting....

[sal21]

I use this sql to group by to count the single quantity of value in TIPO_DATo...
But how to return in a var_00, var_05, var_06 the single qauntity of value?

Code: Select all

SQL = "SELECT TIPO_DATO, COUNT(*) FROM CC_DAILY GROUP BY TIPO_DATO"
Set OCONN_RS = OCONN.Execute(SQL)

note:
I can have in TIPO_DATO only 3 value "00", "05" and "06"
I want to have the counting of "00",I want to have the counting of "05"....

[HansV]

You could loop through the recordset:

Code: Select all

Do While Not OCONN_RS.EOF
  Select Case OCONN_RS(0) ' the TIPO_DATO field
    Case "00"
      var_00 = OCONN_RS(1) ' the count field
    Case "05"
      var_05 = OCONN_RS(1)
    Case "06"
      var_06 = OCONN_RS(1)
  End Select
  OCONN_RS.MoveNext
Loop

[sal21]

You could loop through the recordset:

Code: Select all

Do While Not OCONN_RS.EOF
  Select Case OCONN_RS(0) ' the TIPO_DATO field
    Case "00"
      var_00 = OCONN_RS(1) ' the count field
    Case "05"
      var_05 = OCONN_RS(1)
    Case "06"
      var_06 = OCONN_RS(1)
  End Select
  OCONN_RS.MoveNext
Loop

But is correct my sql?
Have other idea to have the same result?

[HansV]

The SQL is correct as far as I can see.
You could store the values in an array or in a collection instead of instead of in separate variables.

[sal21]

The SQL is correct as far as I can see.
You could store the values in an array or in a collection instead of instead of in separate variables.

Nice tks.

[sal21]

You could loop through the recordset:

Code: Select all

Do While Not OCONN_RS.EOF
  Select Case OCONN_RS(0) ' the TIPO_DATO field
    Case "00"
      var_00 = OCONN_RS(1) ' the count field
    Case "05"
      var_05 = OCONN_RS(1)
    Case "06"
      var_06 = OCONN_RS(1)
  End Select
  OCONN_RS.MoveNext
Loop

HUMMMMMMMMMM...
but if i dont know the name of value, how to assign the sigle counting to the each value???

resolved with:

Code: Select all

Do While Not GAF_RS.EOF
AG = GAF_RS(0)
WS1.Range("A" & RIGA) = Format(AG, "#0000")
NUMERO_C = GAF_RS(1)
WS1.Range("B" & RIGA) = NUMERO_C
RIGA = RIGA + 1
GAF_RS.MoveNext
Loop

Corretct?

[HansV]

Or shorter

WS1.Range("A2").CopyFromRecordset GAF_RS