In UniQuery, how do you get the count of unique values found while doing a BREAK.ON

Question

I know I can get the counts for how many individual entries are in each unique groups of records with the following.

LIST CUSTOMER BREAK-ON CITY TOTAL EVAL "1" COL.HDG "Customer Count" TOTAL CUR_BALANCE BY CITY

And I end up with something like this.

Cust...... City...... Customer Count Currently Owes

         6 Arvada     1                        4.54
           ********** -------------- --------------
           Arvada     1                        4.54

       190 Boulder    1                        0.00
         1 Boulder    1                       13.65
           ********** -------------- --------------
           Boulder    2                       13.65
 ...
                      ============== ==============
TOTAL                 29                      85.28
29 records listed

Which becomes this, after we suppress the details and focus on the groups themselves.

City...... Customer Count Currently Owes

Arvada     1                        4.54
Boulder    2                       13.65
Chicago    3                        4.50
Denver     6                        0.00
...
           ============== ==============
TOTAL      29                      85.28
29 records listed

But can I get a count of how many unique grouping are in the same report? Something like this.

City...... Customer Count Currently Owes City Count

Arvada     1                        4.54          1
Boulder    2                       13.65          1
Chicago    3                        4.50          1
Denver     6                        0.00          1
...
           ============== ============== ==========
TOTAL      29                      85.28         17
29 records listed

Essentially, I want the unique value count integrated into the other report so that I don't have to create an extra report just for something so simple.

SELECT CUSTOMER SAVING UNIQUE CITY

17 records selected to list 0.

Answer 1

I swear that this should be easier. I see various @ variables in the documentation that hint at the possibility of doing this easily but I have never been about to get one of them to work.

If your data is structured in such a way that your id is what you would be grouping by and the data you want is stored in Value delimited field and you don't want to include or exclude anything you can use something like the following.

In UniVerse using the CUSTOMER table in the demo HS.SALES account installed on many systems, you can do this. The CUSTID is the the record @ID and Attribute 13 is where there PRICE is stored in a Value delimited array.

    LIST CUSTOMER BREAK-ON CUSTID TOTAL EVAL "DCOUNT(@RECORD<13>,@VM)" TOTAL PRICE AS P.PRICE BY CUSTID DET.SUP

Which outputs this.

               DCOUNT(@RECORD<13>,@
Customer ID    VM).................    P.PRICE

          1    1                        $4,200
          2    3                       $19,500
          3    1                        $4,250
          4    1                       $16,500
          5    2                        $3,800
          6    0                            $0
          7    2                        $5,480
          8    2                       $12,900
          9    0                            $0
         10    3                       $10,390
         11    0                            $0
         12    0                            $0
               ====================    =======
               15                      $77,020

That is a little juice for a lot of squeeze, but I hope you find it useful.

Good Luck!

Answer 2

I'm not offering a solution but want to point out the RetrieVe keyword for a Count is ENUM. TOTAL EVAL "1" is an unnecessary kludge at least in U2.

LIST CUSTOMER BREAK.ON CITY ENUM @ID

Answer 3

Since the system variable @NB is set only on the total lines, this will allow your counter to calculate the number of TOTAL lines, which occur per unique city, excluding the grand total.

LIST CUSTOMER BREAK-ON CITY TOTAL EVAL "IF @NB < 127 THEN 1 ELSE 0" COL.HDG "Customer Count" TOTAL CUR_BALANCE BY CITY

I don't have a system to try this on, but this is my understanding of the variable.