Count Rows via BQL

If you're only interested in the count of rows matching a condition, try PXSelectReadonly with Aggregate<Count<>>, like this:

PXResultset<GSDocSyncState> countResult = PXSelectReadonly<GSDocSyncState, Where<GSDocSyncState.sFDocumentType, Equal<@P.AsString>>, Aggregate<Count>>.Select(graph, type); int count = 0; if (countResult.Count > 0) { count = (int)countResult[0][typeof(GSDocSyncState)]; } us.updates = count;

You haven’t really indicated why the result you’re getting isn’t the result you want. It looks like you’re attempting to get a count of different types of documents. Is that correct?

Are you getting errors or incorrect counts?

​@CherryStreet 

I am getting a syntax error using.

PXSelectReadonly<Table, Where, OrderBy>

‘….Aggregate<...> can not be used as type parameter OrderBy...’

Also, I assume I can just count any field.

Aggregate<Count<GSDocSyncState.sFDocumentID>>>

Ahh..you’re hitting that error because Aggregate<> and OrderBy<> can’t be used together in a PXSelect — they occupy the same positional argument in the select definition, so you have to choose one or the other. If you’re trying to count rows, just drop the OrderBy<> entirely.

See if this works...taking off so I’ll offline for a bit.
 

PXResultset<GSDocSyncState> countResult = PXSelectReadonly<GSDocSyncState,
    Where<GSDocSyncState.sFDocumentType, Equal<@P.AsString>>,
    Aggregate<Count<GSDocSyncState.sFDocumentID>>>.Select(graph, type);

int count = 0;
if (countResult.Count > 0)
{
    count = (int)countResult[0][typeof(Count<GSDocSyncState.sFDocumentID>)];
}
us.updates = count;

Try this:

using PX.Data.BQL.Fluent;
using System.Linq;

var goodSel = SelectFrom<GSDocSyncState>.
Where<GSDocSyncState.sFDocumentType.IsEqual<P.AsString>>.
View.Select(graph, type)?.FirstTableItems;

int count = goodSel?.GroupBy(d => d.SFDocumentType)?.Count();

​@darylbowman 

Question, Does that group via SQL or C#? in other words is the group by function executed at the database level or application level?

I am worried it is at the application level and requires downloading the whole result set.

That’s C#. You can also try this:

using PX.Data.BQL.Fluent;
using System.Linq;

var goodSel = SelectFrom<GSDocSyncState>.
    Where<GSDocSyncState.sFDocumentType.IsEqual<P.AsString>>.
    AggregateTo<GroupBy<GSDocSyncState.sFDocumentType>>.
    View.Select(graph, type)?.FirstTableItems;

int count = goodSel?.Count();

Thanks guys, I ended up with your help with this.

PXResultset<GSDocSyncState> countResult = PXSelectGroupBy<GSDocSyncState,
Where<GSDocSyncState.sFDocumentType, Equal<@P.AsString>>,
Aggregate<Count<GSDocSyncState.sFDocumentID>>>.Select(graph, type);

int cnt = 0;
if (countResult.Count > 0)
{
	cnt = (int)countResult[0].RowCount;// [typeof(Count<GSDocSyncState.sFDocumentID>)];
}

Yeah, now that I think about it, my suggestion doesn’t make sense. When doing ‘Sum’, you can get the summed value from the result (from the field being summed). With counts, you apparently use RowCount. Here’s an F-bql aggregate using ‘Count’ from the source code:

var orderCount = SelectFrom<SOOrder>
    .Where<SOOrder.customerID.IsEqual<@P.AsInt>>
    .AggregateTo<Count<SOOrder.orderNbr>>
    .View.Select(graph, entry.GuestAccountID).RowCount.Value;