Count distinct doesn't work when using OrderBy & join

user_657477 0
Viewed 39

I have the following query trying to get count of a query:

var testQuery = Db
    .From<Blog>()
    .LeftJoin<BlogToBlogCategory>()
    .Where(x => x.IsDeleted == false)
    .OrderBy(x => x.ConvertedPrice);

var testCount = Db.Scalar<int>(testQuery.Select<Blog>(x => Sql.CountDistinct(x.Id)));

var results = Db.LoadSelect(testQuery.SelectDistinct());

It gives error:

42803: column "blog.converted_price" must appear in the GROUP BY clause or be used in an aggregate function

Issue seems to be the orderby statement. If I remove it then the error goes away. Why does this stop count distinct working?

I am having to clear orderby on all queries I do like this. Is it supposed to work this way?

Also I just realised count is wrong. Results is 501 unique records and testCount is 538.

What am I doing wrong?

servicestack
1 Answers

Whenever in doubt with what an OrmLite query is generating, you can use the BeforeExecFilter to inspect the DB command before its executed or to just output the query to the Console you can use:

OrmLiteUtils.PrintSql();

You shouldn't be using OrderBy with aggregate scalar functions like COUNT which is meaningless and will fail in your case because it needs to included the GROUP BY clause for joined table queries.

Your specifically querying for COUNT(DISTINCT Id) if you wanted the row count for the query you can instead use:

var testCount = Db.RowCount(testQuery);

If you wanted to use COUNT(*) instead, you can use:

var testCount = Db.Count(testQuery);
user_857850
Related