FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

I tested sqsh, tsql, bsqldb, and fisql with this query:

select uid, usertype, type 
from systypes 
where type in (
        select type 
        from systypes 
        group by type 
        having count(*) > 1
) 
order by type, uid, usertype 
compute sum(uid) by type 
compute count(usertype) by type 
compute avg(type)

None of them produced satisfactory results.  sqsh probably comes closest;
I suspect ct-lib needs a little work in that department.  Here are the
results:

sqsh:

[29] FreeTDS.1> select uid, usertype, type from systypes where type in
(select type from systypes group by type having count(*) > 1) order by
type, uid, usertype compute sum(uid) by type compute count(usertype) by
type compute avg(type)
[29] FreeTDS.2> go
 uid         usertype type
 ----------- -------- ----
           1        4   37
           1       80   37
                          
 -----------              
           2              
                          
             --------     
                    2     
 uid         usertype type
 ----------- -------- ----
           1        2   39
           1       18   39
           1       25   39
                          
 -----------              
           3              
                          
             --------     
                    3     
 uid         usertype type
 ----------- -------- ----
           1        1   47
           1       24   47
                          
 -----------              
           2              
                          
             --------     
                    2     
                          
                      ====
                        40

(14 rows affected)

Analysis: sqsh is missing the aggregating operator and the column it
operates on.  It does line the compute columns under the right rows,
though, which helps.  

tsql:

1> :r fancy_compute.sql 
3> go
uid     usertype        type    
1       4       37      
1       80      37      
2       
2       
1       2       39      
1       18      39      
1       25      39      
3       
3       
1       1       47      
1       24      47      
2       
2       
40      

Analysis: tsql is not a general purpose query tool.  Good thing.  

bsqldb:

]$ bsqldb -S $S -U $U -P $P -i fancy_compute.sql 
        uid  usertype  type
-----------  --------  ----
          1         4    37
          1        80    37
sum(usertype)
-------------
            2
count(type)
-----------
          2
          1         2    39
          1        18    39
          1        25    39
sum(usertype)
-------------
            3
count(type)
-----------
          3
          1         1    47
          1        24    47
sum(usertype)
-------------
            2
count(type)
-----------
          2
avg((null))
-----------
         40
14 rows affected

Analysis: Most metadata of the lot.  bsqldb is not meant for interactive
viewing, so the compute rows are intentionally left-justfified.  Only the
last compute column name is missing; I suspect a db-lib bug.  

fisql:

1>> :r fancy_compute.sql
Segmentation fault (core dumped)

$ printf "%s\ngo\n" "$(cat ../fancy_compute.sql)" | fisql  -S $S -U $U -P
$P 
Msg 5701, Level 10, State 2:
Server 'test', Changed database context to 'FreeTDS'.

uid         usertype type 
        
----------- -------- ---- 
        
1           4        37   
        
1           80       37   
        
1           80       37   
        
1           80       37   
        
1           2        39   
        
1           18       39   
        
1           25       39   
        
1           25       39   
        
1           25       39   
        
1           1        47   
        
1           24       47   
        
1           24       47   
        
1           24       47   
        
1           24       47   
        

(14 rows affected)

Analysis: When the compute rows arrive, the preceding data row is
reprinted.  

--jkl