BTEQ window vs SQL Assistant for "With Count" queries

Database

BTEQ window vs SQL Assistant for "With Count" queries

Hello all,

I'm doing the Advanced SQL course and I'm doing the following "with count function:
SEL t.load_dt, t.counter FROM
(
SEL
load_dt, COUNT(load_dt) AS counter
FROM DB.table
GROUP BY 1
)AS t
ORDER BY 2,1
WITH SUM(t.counter)(TITLE 'Total Count');

I get:
load_dt counter
-------- -----------
10/07/30 8
10/06/29 2248
10/07/29 6230
10/05/26 6508
10/04/30 8761
09/11/11 23445
10/01/07 25178
10/03/03 28811
09/10/08 29548
10/03/30 31456
-----------
Total Co 162193

in the BTEQ window (which is what I want)
but in SQL assistant the total count (with count function) does not display an output row:
load_dt counter
2010-07-30 8
2010-06-29 2248
2010-07-29 6230
2010-05-26 6508
2010-04-30 8761
2009-11-11 23445
2010-01-07 25178
2010-03-03 28811
2009-10-08 29548
2010-03-30 31456

What do I need to do to get SQL Assistant to display the same output?

1 REPLY
N/A

Re: BTEQ window vs SQL Assistant for "With Count" queries

BTEQ was used as a report generator years ago, that's why WITH was implemented.
But you're probably not going to write reports with BTEQ anymore.

SQL Assistant always removes the rows produced by WITH, so you can't get that output :-)

There's a simple reason for it:
Those intermediate rows have a different row layout, they simply don't fit into the display grid.

MS SQL Server has a similar construct, COMPUTE BY, and osql returned a readable answer set only when using text format.

Nowadays that kind of information is retrieved using SUM/COUNT OVER, which is Standard SQL, but returns a column instead of a row.

If you really want that extra row, you could use GROUP BY GROUPING SETS, untested:
SEL
load_dt, COUNT(load_dt) AS counter
FROM DB.table
GROUP BY grouping set ((load_dt),())
ORDER BY grouping(load_dt), 2,1

Dieter