Unexpected behavior when creating Virtual layer using PyQGIS

Question

I have already viewed this link and still having issues - Creating Virtual Layer with PyQGIS.

When I create a virtual layer from the Layer -> Add/edit virtual layer and use this as a query it works as expected, i.e. column 1 contains the subtype, and column 2 is the sum of lengths of each subtype

select "Subtype", sum(Length)
from 'roads'
group by "Subtype"

However, when I use the console and paste this, I only get one row where column 1 is just called a "Subtype" and column 2 has the total length of all subtypes. How can I get the same results using Python console script as I do when manually creating a virtual layer using the menu?

from qgis.core import QgsVectorLayer, QgsProject
vlayer = QgsVectorLayer("?query=select 'Subtype', sum(Length) from 'roads' group by 'Subtype'", "vlayer", "virtual")
QgsProject.instance().addMapLayer(vlayer)

Answer 1

You need to remove the single quotes, or use double quotes for field name instead.

"?query=select Subtype, sum(Length) from 'roads' group by Subtype"

or

"?query=select \"Subtype\", sum(Length) from 'roads' group by \"Subtype\""