sql partiton by suppress repeating values

Question

Hello,

Using SQL Server (2005/2008)

Is there an easy way to suppress the repeating sub & grand totals?

select h.salesmanno ,c.custname ,h.amt ,sum(h.amt) over (partition by salesmanno) as subtotal
,sum(h.amt) over (partition by null) as grandtotal
from hdr h, cust c WHERE h.custno = c.custno and h.hdate = '6/8/2009'

basically suppress (or null) except the last position, before the group change

Thanks!!

Answer 1

Nest this in a subquery and add your filter in a WHERE:

SELECT *
FROM (
select h.salesmanno ,c.custname ,h.amt ,sum(h.amt) over (partition by salesmanno) as subtotal
,sum(h.amt) over (partition by null) as grandtotal
from hdr h, cust c WHERE h.custno = c.custno and h.hdate = '6/8/2009' 
) AS X
WHERE whatever

Answer 2

What database is this? On SQL Server, something like this would do:

select
  salesmanno, custname, amt
, case when subtotal_no = subtotal_count then subtotal end as subtotal
, case when total_no = total_count then subtotal end as total
from (
  select 
    h.salesmanno 
  , c.custname 
  , h.amt 
  , sum(h.amt) over (partition by salesmanno) as subtotal
  , sum(h.amt) over (partition by null) as grandtotal
  , row_number() over (partition by salesmanno order by custname) as subtotal_no
  , row_number() over (partition by null order by salesmanno, custname) as as total_no
  , count(*) over (parition by salesmanno) as subtotal_count
  , count(*) over (parition by null) as total_count
  from hdr h, cust c 
  WHERE h.custno = c.custno 
    and h.hdate = '6/8/2009'
  ) a
order by total_no

Shorter version, more sorting for the database, maybe less obvious what's going on:

select
  salesmanno, custname, amt
, case when subtotal_no = 1 then subtotal end as subtotal
, case when total_no = 1 then subtotal end as total
from (
  select 
    h.salesmanno 
  , c.custname 
  , h.amt 
  , sum(h.amt) over (partition by salesmanno) as subtotal
  , sum(h.amt) over (partition by null) as grandtotal
  , row_number() over (partition by salesmanno order by custname desc) as subtotal_no
  , row_number() over (partition by null order by salesmanno desc, custname desc) as as total_no
  from hdr h, cust c 
  WHERE h.custno = c.custno 
    and h.hdate = '6/8/2009'
  ) a
order by total_no desc

Alternatively, using ROLLUP to generate subtotal and total rows:

  select 
    h.salesmanno 
  , c.custname 
  , sum(h.amt) as amt
  from hdr h, cust c 
  WHERE h.custno = c.custno 
    and h.hdate = '6/8/2009'
  group by 
    h.salesmanno 
  , c.custname 
  with rollup 
  order by
    h.salesmanno 
  , c.custname

To get the results in the proper order, change the order by to something like this:

  order by
    grouping(h.salesmanno)
  , h.salesmanno
  , grouping(c.custname)
  , c.custname