COUNT(*)?

[johnqin]

I want to perform the following query.

SELECT ColumnName, COUNT(*) FROM TableName GROUP BY ColumnName

Not sure how to invoke COUNT(*) in SquiDB. Appreciate some guidance. thanks

[sbosley]

I think you want IntegerProperty.countProperty:

IntegerProperty count = IntegerProperty.countProperty("count");
Query query = Query.select(Model.COLUMN, count).groupBy(Model.COLUMN);
// This compiles to:
// select model.column as column, count(*) as count from model_table group by model.column
// The from clause is filled in automatically by the call to database.query

SquidCursor<Model> cursor = database.query(Model.class, query);
try {
    while(cursor.moveToNext()) {
        String columnValue = cursor.get(Model.COLUMN);
        int count = cursor.get(count);
    }
} finally {
    cursor.close();
}

Hope that helps!

[johnqin]

Is Function.count().as("count") an alternative method?

[sbosley]

Yes, but using Property objects is what gets you some of the free type safety functionality. So if you use Function.count().as("count"), the code to read the value from the cursor is slightly less pretty:

Query query = Query.select(Model.COLUMN, Function.count().as("count")).groupBy(Model.COLUMN);

SquidCursor<Model> cursor = database.query(Model.class, query);
try {
    int countIndex = cursor.getColumnIndexOrThrow("count");
    while(cursor.moveToNext()) {
        String columnValue = cursor.get(Model.COLUMN);
        int count = cursor.getInt(countIndex);
    }
} finally {
    cursor.close();
}

We tend to prefer the IntegerProperty version, but both are perfectly valid depending on which style you prefer.

[johnqin]

thanks!

[sbosley]

No problem! I'm assuming this question is resolved so I'm going to close the issue, but feel free to reopen it if you have other questions.