Generating Derived Table

[Former Member]

Hi,

I've just created in BO Designer 6.5 a derived table for the calculation of an aggregate function.

The table is structured in the following way (for example):

SELECT SUM(ColumnCount) AS Sum_Distinct_NDG

FROM (SELECT COUNT(DISTINCT cod_sample) AS ColumnCount

FROM table.sample) DTBL

In the derived table I have only one numeric column, and I can not put it in join with my fact table (table.sample).

I thought topopulate the derived table also with an "alias" of the "table.sample.cod_sample" to put them in join, am I right? Anyway... I don't know how to do it.

Can anybody help me to go on?

Thanks in advance

Riccardo

[former_member142702 · Accepted solution]

Hi,

can you please describe what exactly is your requirement here?

Your example is a little bit confusing since your sub-select in the FROM clause ie. SELECT COUNT(DISTINCT cod_sample) AS ColumnCount FROM table.sample returns just one line and the outer aggregation SUM(ColumnCount) does not really make a difference.

Regards,

Stratos

[Former Member · Accepted solution]

Hi Stratos,

the aim is "to count the cod_sample (in my example) distincly and then to sum them to have the total of the counting".

I tried to specified n Designer my object with the following select statement:

sum(count(distinct(cod_sample)))

but the SQL can not perform the aggregate function on an expression that is already an aggregate.

Regards,

Riccardo

[former_member142702 · Accepted solution]

Hi riccardo,

assuming you have a table A with a column A1 and you want to count the distinct entries in this column. Your sql statement will look like this:

select count(distinct A.A1) from A

Assuming that the column A1 contains the following data:

A1

1

1

2

2

3

Your sql statement return only one row with just one column:

select count(distinct A.A1) from A

3

In your example you try then to build the sum of a table with a single line:

select sum(B.alias1) from ( select count(distinct A.A1) as alias1 from A ) B

which does not make really sense.

Generally it does not make sense to apply two aggregation operators on the same database field at once.

What is your high level requirement here? In order to count the distinct entries the distinct count operator should be enough.

Regards,

Stratos

[Former Member · Accepted solution]

Hi Stratos,

Thank you for the explanation,

I was... overstimating the problem

Solved problem star

Regars,

Riccardo