ADS 17.0.7 – distinct COUNT() using the Query Builder

by Nov 4, 2016

Hi all. I have created a query in the query builder with a HAVING condition of COUNT(CLAIM_NUMBERS) > 1. Essentially trying to view a population that has more than one claim. This is not sufficient because it will count all line items because the the DISTINCT operator is not in the COUNT function (COUNT(DISTINCT CLAIM_NUMBERS).

Obviously, this is easily solvable using raw SQL, however I am instructing a population that is more inclined towards using the Query Builder. I was hoping there is a solution for this as this type of scenario is quite common in our workplace.

We’re using Netezza and Aqua Data Studio v 17.0.7 if that helps at all.

Thanks!

Ted

Response

Jenny Nishimura over 6 years ago
Query Builder currently doesn’t provide an option to choose the DISTINCT operator in the Having deck. However, Query Builder allows you to create a custom expression.

Option 1. When you drag and drop a field to the Having deck, the Edit Having Criteria dialog is displayed. In this dialog, you can enter an Expression. Please refer to the attachment countDistinct1.png.

Option 2. Choose the menu option “Create SQL Expression…” in the Having deck. In the Create SQL Expression dialog, you can enter the HAVING clause. Please refer to the attachment countDistinct2.png.

Would either of these options work for your users?

Ted Leib over 6 years ago
Thanks Jenny! That did the trick ^_^