Can you group by a portion of a field in a SQL Statement?

Mahares said:

I need to GROUP BY the first 6 characters of the field not the entire field:

BELOW WORKS USING THE ENTIRE FIELD:
txt = "SELECT sum(MCFD) AS MC ,avg(STATP)AS LP FROM MY_TABLE WHERE MYFIELD between '" _
& DS & "'" & " AND '" & DE & "' AND ITEM_NO LIKE '" & XYZ & "'" _
& " GROUP BY MYFIELD "

BELOW DOES NOT WORK USING THE FIRST6 CHARACTERS OF THE FIELD:
txt = "SELECT sum(MCFD) AS MC ,avg(STATP) AS LP FROM MY_TABLE WHERE MYFIELD between '" _
& DS & "'" & " AND '" & DE & "' AND ITEM_NO LIKE '" & XYZ & "'" _
& " GROUP BY MYFIELD.substring2(0,6) AS MY_SHORT_FIELD"

Of course I cannot use substring2 in a sql statement, but what do you use?

Click to expand...

GROUP BY SUBSTRING(MYFIELD, 6)

Click to expand...

SELECT sum(MCFD) AS MC ,avg(STATP) AS LP, FROM MY_TABLE WHERE MYFIELD between '" _
& DS & "'" & " AND '" & DE & "' AND ITEM_NO LIKE '" & XYZ & "'" _
& " GROUP BY MYFIELD HAVING (SUBSTRING(MYFIELD, 6))"

Click to expand...

You and Klaus are not human.

Click to expand...