StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What database is this? On SQL Server, something like this would do:</p> <pre><code>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 </code></pre> <p>Shorter version, more sorting for the database, maybe less obvious what's going on:</p> <pre><code>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 </code></pre> <p>Alternatively, using ROLLUP to generate subtotal and total rows:</p> <pre><code> 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 </code></pre> <p>To get the results in the proper order, change the order by to something like this:</p> <pre><code> order by grouping(h.salesmanno) , h.salesmanno , grouping(c.custname) , c.custname </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload