i have a table with primary keys more than one field. How do I get count distinct for these primary keys. see below :

return (int) adapter.GetScalar( ?, // PK1, PK2, ...PKn f null, AggregateFunction.CountDistinct, bucket.PredicateExpression, null, bucket.Relations);

this method get just one field. How do i set more than one field to get count distinct on them!

Hi Stranger

Try this. I have copied it from the manual

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFieldIndex.Country, 0, "Country");
fields.DefineField(CustomerFieldIndex.CustomerID, 1, "AmountCustomers");
fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;

fields[1].AggregateFunctionToApply = AggregateFunction.CountDistinct;

what about field[0]? getscalar gets the aggregateFunctionToApply, what should be set there?

Using "Order Details" table from Northwind database as a model. I assume you want to do something as follows:

SELECT Count(Distinct OrderID), Count(Distinct ProductID)
FROM [Order Details]

Where OrderID & ProductID are both part of the PK.

If so, then please try the following example:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(Order DetailsFieldIndex.OrderId, 0, "Count1", "", AggregateFunction.CountDistinct);
fields.DefineField(Order DetailsFieldIndex.ProductId, 1, "Count2", "", AggregateFunction.CountDistinct);

dynamicList = new DataTable();
DataAccessAdapter DAA = new DataAccessAdapter();
DAA.FetchTypedList(fields, dynamicList, null, 0, null, false, null);

I think you dont get my problem.

let me explain.

suppose we have a table with PK=(pk1, pk2, pk3, pk4) and another field f.

pk1 pk2 pk3 pk4 f 1 2 3 1 9 1 2 3 2 9 1 2 3 3 9 1 2 4 1 9 1 2 4 2 9

i want the count of rows that 'f = 9' and it should be distinct for f1, f2, f3 together. for examle for the above data it should return 2.

pk1 pk2 pk3 1 2 3 1 2 4

I want just the count of rows. Is there a solution to get just the count without fetching and then counting them?

Stranger wrote:

I think you dont get my problem.

let me explain.

suppose we have a table with PK=(pk1, pk2, pk3, pk4) and another field f.

pk1 pk2 pk3 pk4 f 1 2 3 1 9 1 2 3 2 9 1 2 3 3 9 1 2 4 1 9 1 2 4 2 9

i want the count of rows that 'f = 9' and it should be distinct for f1, f2, f3 together. for examle for the above data it should return 2.

pk1 pk2 pk3 1 2 3 1 2 4

I want just the count of rows. Is there a solution to get just the count without fetching and then counting them?

select count(pk4) from table where f=9 group by pk1, pk2, pk3

I think he meant

select distinct pk1, pk2, pk3 from Table
where f = 9

in this case just specify 'false' for allowDuplicates

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(YourEntityFieldIndex.PK1, 0, "PK1");
fields.DefineField(YourEntityFieldIndex.PK2, 1, "PK2");         
fields.DefineField(YourEntityFieldIndex.PK3, 2, "PK3");

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(YourEntityFieldIndex.f, ComparisonOperator.Equal, 9));

dynamicList = new DataTable();

DataAccessAdapter DAA = new DataAccessAdapter();
DAA.FetchTypedList(fields, dynamicList, bucket, 0, null, false, null);

dear walaa

it is the exaclty query i want. but I want just count of resulted rows. I dont want to fetch them and then apply COUNT on it! it is time consuming!

Cheers SAEED

:> So your goal is:

select count(*)
from
(
select distinct pk1, pk2, pk3 from Table
where f = 9
)T

??

Stranger wrote:

dear walaa

it is the exaclty query i want. but I want just count of resulted rows. I dont want to fetch them and then apply COUNT on it! it is time consuming!

Cheers SAEED

Count is an aggregatefunction, you want to count rows, and because the rows have multiple fields, you should group by. Please run the query I posted, I think it will bring you the results you want.

Walaa: that query will also give the same results I think, but you need to select from a selectlist, which cant be done in llblgen pro yet

As an SQL-related aside, it isn't necessary to pass one or more columns to the count aggregate function. It simply counts the instances of the column. Using count(1) can (ever so slightly) improve the performance of the query if the column is not used anywhere else, especially if the column contains large amounts of data per row.