Creating a universe, I have an object for Count the row ID. When I use the integrity checker, I get the error “Count(ID) contains a count without a distinct. Duplicate rows are possible”
For my SQL, I simply have count(table1.ID). Each row ID is unique so it should be a problem, but I would like to know how to modify this query so as to bring back no erros when I check for integrity.
fghs_1987 
(BOB member since 2008-01-02)
You can ignore the Integrity Checker message in this situation. If you really want to stop the message, change your sql to count(distinct rowid). That is a bit silly though 
Nick Daniels (BOB member since 2002-08-15)
that worked!! many thanks for such a quick response!
fghs_1987 (BOB member since 2008-01-02)
It may work, but is “silly” as Nick suggested 
.
It may solve your integrity check error, but using a distinct in your queries will cause more work for the database.
As you already have a unique ID, just ignore the Designer error and do a straight count instead…
Mak 1 (BOB member since 2005-01-06)
The reason you need a distinct is because even though the ID might be unique on that specific table, if you have a fan trap (the count combined with another table at a lower level of detail) you will not get the correct answer.
A “best practice” for universe design is to always use a distinct on a count, thus the warning in the Integrity Check.
Dave Rathbun 
(BOB member since 2002-06-06)
True, well, as long as long as you set the aggregation to “None” under object properties.
You will then have to create the sum of the count distinct on the report for the correct answer, if slicing and dicing.
This is a difficult one for me, do you educate users in the data that will cause the problem, maybe 10%, or do you make them sum at the report level for all the reports that are going to be built?
Mak 1 (BOB member since 2005-01-06)
Why do they have to sum at the report level? :-?
The measure works like any other measure, just with the distinct it will be correct in all cases, as long as you count a unique ID. Picture a table with customers with a customer_id, and you count them. You get 20. Now each customer has 3 orders, so you include some object from the orders table… now your count returns 60 rather than 20. A count(distinct customer.customer_id) will still return 20, and be correct.
It has nothing to do with the projection function, which can still be set to sum.
Dave Rathbun (BOB member since 2002-06-06)
Sorry Dave,
I meant do the count distinct at the report level 
.
I recently experienced a problem with this where a subscription can legitmatedly can have two product IDs.
Including my count distinct against various product attributes with two rows was counting a distinct for each row. Obviously when slicing and dicing it produced a count of two subscirptions rather than the one.
Having projection set to none produced the right result, but was not right for the majority of the reports where aggregation was required.
Therefore I went for creating the distinct at the report level instaed which produced the right result…:).
Andreas touches on this situation here:-
https://bobj-board.org/t/71712
Mak 1 (BOB member since 2005-01-06)