Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Complex Query with SQL COUNT and SUM

I'm trying to build the following query with Ormlite:

SELECT Column1,
      COUNT(Column2),SUM(Column2)

  FROM Table

  WHERE Column3 = 1

  GROUP BY Column1;

I'm using QueryBuilder for this, but I can't figure out to get the sum and still have one whole list as a result.

What type would this result list be? I can't make it List since sum and count are not columns in the table.

like image 879
user1851212 Avatar asked Feb 14 '14 12:02

user1851212


2 Answers

I'm using this way

QueryBuilder<UsageStats, Integer> b = dao.queryBuilder();
b.selectRaw("SUM(" + UsageStats.COLUMN_VALUE + ")");
b.groupBy(UsageStats.COLUMN_TYPE);
b.where().eq(UsageStats.COLUMN_TYPE, type.toString());
dao.queryRawValue(b.prepareStatementString());
like image 183
ATom Avatar answered Sep 30 '22 10:09

ATom


I'm using QueryBuilder for this, but I can't figure out to get the sum and still have one whole list as a result.

Once you use COUNT or SUM then you should use a raw query instead of a query that returns entities. With a raw query you can either get the string result columns directly or map the selected columns and values using a row mapper.

See the docs here:

http://ormlite.com/docs/raw-queries

like image 27
Gray Avatar answered Sep 30 '22 09:09

Gray