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

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>

CodePudding user response：

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.

CodePudding user response：

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.