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

[Mahares]

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?

 

[r2d4]

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?

GROUP BY SUBSTRING(MYFIELD, 6)

Or if this does not work (think GROUP BY does not allow functions:

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))"

 

[Mahares]

Sorry. Your suggestion did not work. The error returned: No such function. I tried similar functions to that before such as left(MYFIEL,6) and others. But none work. This is very different from SQL Server or Access.

 

[NJDude]

Take a look at THIS website, you might find what you need.

 

[Mahares]

Thank you NJDude. You, directing me to that web site is very helpful.
I can use the following syntax to generate what I want and it works. I am going to get a little deeper with this, but this part been my stumbling block. You and Klaus are not human.

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 SUBSTR(MYFIELD ,0,6) "

 

[NJDude]

You and Klaus are not human.

I don't know about Klaus but all I can say to you is: Live long and prosper and may the Force be with you.

 

[klaus]

We all were beginners one day .

Best regards.

 

[Mahares]

Slight correction:
To group by the fist 6 characters of a field, use: GROUP BY SUBSTR(MYFIELD ,1,6)
instead of GROUP BY SUBSTR(MYFIELD ,0,6)