How I can two subtotals summarize in the table SQL Server Managment Studio 2014?

Question

I have an SQL query which returns orderid and quantity ordered for all orders with an orderid >= 11075 and orderid <= 11076:

select orderid, quantity 
    from [order details]
where ((orderid >= 11075) AND (orderid <= 11076))

My ResultSet is:

But I need subtotals in my table. I need to generate a control-break report that provides the total quantity for order numbers 11075 and 11076. COMPUTE and COMPUTE BY don't work in my version of SQL Server. How can I implement this?

Such a table like:

table, th, td {
  border: 1px solid black;
}

<table>
  <tr>
    <th>orderid</th>
    <th>quantity</th>
  </tr>
  <tr>
    <td>11075</td>
    <td>10</td>
  </tr>
  <tr>
    <td>11075</td>
    <td>30</td>
  </tr>
   <tr>
    <td>11075</td>
    <td>2</td>
  </tr>
  <tr>
    <td></td>
    <td>Sum</td>
  </tr>
  <tr>
    <td></td>
    <td>=======</td>
  </tr>
  <tr>
    <td></td>
    <td>42</td>
  </tr>
  <tr>
    <td> </td>
    <td> </td>
  </tr>
  <tr>
    <td>11076</td>
    <td>20</td>
  </tr>
  <tr>
    <td>11076</td>
    <td>20</td>
  </tr>
  <tr>
    <td>11076</td>
    <td>10</td>
  </tr>
  <tr>
    <td></td>
    <td>Sum</td>
  </tr>
  <tr>
    <td></td>
    <td>===========</td>
  </tr>
  <tr>
    <td></td>
    <td>50</td>
  </tr>
</table>

this looks like a presentation issue. Should do it in your frontend application and not in `SQL Server` — Squirrel, Nov 19 '21 at 04:51
@Squirrel I don't have web-application. This is example of a table that I want to output when run query. — stfxc, Nov 19 '21 at 04:58

score 2 · Answer 1 · answered Nov 19 '21 at 11:04

You can generate the results you want using GROUPING SETS and some conditional unpivoting

SELECT
  v.orderid,
  v.quantity
FROM (
    SELECT
      orderid,
      quantity = SUM(quantity),
      grouping = GROUPING(quantity)
    FROM YourTable t
    GROUP BY GROUPING SETS
      (
        (orderid, quantity),
        (orderid)
      )
) t
CROSS APPLY (
    SELECT *
    FROM (VALUES
        (NULL, 'SUM', 1),
        (NULL, '====', 2),
        (NULL, '', 4)
    ) v(orderid, quantity, ordering)
    WHERE t.grouping = 1
    UNION ALL
    SELECT
      CASE WHEN t.grouping = 0 THEN t.orderid END,
      CAST(t.quantity AS varchar(20)),
      3
) v
ORDER BY
  t.orderid,
  t.grouping,
  v.ordering;

db<>fiddle

To create the table you want, you could use FOR XML, however it would be pretty complex, and probably easier done in another language.

buddemat · Answer 2 · 2021-11-19T13:51:01.267

As commented by others, this seems to be more of a presentation issue.

However, you can build a query that produces a result similar to what you want using ROW_NUMER() and GROUP BY ROLLUP:

  select orderid
       , case when orderid is null then'grand total' else isnull(try_convert(varchar,rn),'subtotal') end as rn
       , sum(quantity) as quantity
    from (
           select *, ROW_NUMBER() OVER (partition by orderid order by orderid) as rn
             from order_details
            where ((orderid >= 11075) AND (orderid <= 11076)) 
         ) tmp
group by rollup (orderid, rn);

This will also include a grand total row at the end.

orderid	rn	quantity
11075	1	10
11075	2	30
11075	3	2
11075	subtotal	42
11076	1	20
11076	2	20
11076	3	10
11076	subtotal	50
	grand total	92

Note: if you already have some kind of other column that is unique within each orderid, like for instance a unique id column, you could also omit the ROW_NUMBER() and use that column instead of rn in the GROUP BY ROLLUP directly.

See this db<>fiddle.

How I can two subtotals summarize in the table SQL Server Managment Studio 2014?

2 Answers2