Search code examples
sqlqgis

Get count and list of unique values from table without listing each column using SQL in QGIS

Based on questions like SQL to find the number of distinct values in a column and https://gis.stackexchange.com/questions/330932/get-line-length-using-sql-in-qgis

I see we can get a count and list of unique values using SQL but I can't see anything where we can do this without knowing the name of the field.

Is it possible in SQL for QGIS which only allows these commands? I found this option for another flavor -https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

In Mapbasic I have used the following but would like to do this in SQL...

'Get Column Name list
dim x as integer
dim sColName as string
dim aColName as Alias
For x=1 to TableInfo(temptable, TAB_INFO_NCOLS)
    sColName = ColumnInfo(temptable, "col"+str$(x), COL_INFO_NAME)
    if (sColName not in ("GID","GID_New")) then
        aColName = sColName

        Select aColName, count(*) from temptable group by aColName into "g_"+sColName
        Browse * from "g_"+sColName
        Export "g_"+sColName Into WFolder+RSelection.col2+"_"+sColName+".csv" Type "ASCII" Delimiter "," CharSet "WindowsLatin1" Titles
    End If
Next

I guess in SQL we would use http://www.sqlservertutorial.net/sql-server-basics/sql-server-select-distinct/ but how can I tell it to just use every column in the table without knowing/specifying the name?

UPDATE

If I run 

SELECT DISTINCT * FROM Drainage_Lines_Clip;

I get enter image description here

But I need something like the following without having to specify the column name. Ref enter image description here

It should look like this extract from running Unique on a google sheet of the data (except with counts) enter image description here

Solution

  • I ended up having to use a combination of PyQGIS and SQL to get what's needed.

    layer = qgis.utils.iface.activeLayer()
fields=[] # List of fields
Lquery=[] # List of queries to join together with Union All statement
Cquery=[] # Combined Query to use
for field in layer.fields():
    if field.name() not in ('GID_New'):
        fields.append(field.name())
        query = "Select '{0}' as 'Column', {0} as 'Value', count(*) as 'Unique' from {1} group by {0}".format(field.name(), layer.name())
        Lquery.append(query)
    else:
        print (field.name())
#    query = "Select {0}, count(*) from {1} group by {0} order by 2 Desc".format(field.name(), layer.name())

for L in Lquery:
    Cquery.append(L+' Union All ')
query=''.join(map(str, Fquery))
query=query[:-11]+' Order by Column'
vlayer = QgsVectorLayer( "?query={}".format(query), 'counts_'+layer.name(), "virtual" )
QgsProject.instance().addMapLayer(vlayer)