在價格列下方而不是在其自己的列中添加總和

我在我的數據庫上執行這個選擇查詢：

Select Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate,
Sum(bid.bidPrice) over () as TotalBids
from Bid
inner join Item on bid.ItemId=Item.ItemId
where BidDate between '2016-08-24' and '2017-11-15'

我得到以下結果：

ItemId  ItemDescription BidPrice    BidDate     TotalBids
1       Frame           35         2016-08-24   3624
4       Wooden chair    40         2016-10-25   3624
2       Car             3000       2017-10-26   3624
3       Stand Fan       29         2017-10-30   3624
5       Black Sofa      400        2017-11-11   3624
6       Cabinet         120        2017-11-15   3624

我的問題是：是否有可能不是Total Bids每行都有一個總計的列，而是在列的底部得到一個總計BidPrice？

您可以使用 a GROUP BY GROUPING SETSover NULLset

SELECT itemid, itemdescription, biddate, totalbids, sum(bidprice)
FROM f
GROUP BY GROUPING SETS ( (itemid,itemdescription,biddate,totalbids), () );
itemid | itemdescription |  biddate   | totalbids | sum  
--------+-----------------+------------+-----------+------
     1 | Frame           | 2016-08-24 |      3624 |   35
     2 | Car             | 2017-10-26 |      3624 | 3000
     3 | Stand Fan       | 2017-10-30 |      3624 |   29
     4 | Wooden chair    | 2016-10-25 |      3624 |   40
     5 | Black Sofa      | 2017-11-11 |      3624 |  400
     6 | Cabinet         | 2017-11-15 |      3624 |  120
       |                 |            |           | 3624
(7 rows)

已驗證可與 PostgreSQL 一起使用。SQL Server 2014 和 SQL Server 2016。

CREATE TABLE f(
 itemid int,
 itemdescription varchar(255),
 bidprice int,
 biddate date,
 totalbids int
)
INSERT INTO f VALUES
   ( 1, 'Frame       ', 35  , '2016-08-24', 3624 ),
   ( 4, 'Wooden chair', 40  , '2016-10-25', 3624 ),
   ( 2, 'Car         ', 3000, '2017-10-26', 3624 ),
   ( 3, 'Stand Fan   ', 29  , '2017-10-30', 3624 ),
   ( 5, 'Black Sofa  ', 400 , '2017-11-11', 3624 ),
   ( 6, 'Cabinet     ', 120 , '2017-11-15', 3624 );

PostgreSQL的文件更好地解釋了這一點

另一種方法是使用UNION,

WITH t AS (SELECT * FROM f)
SELECT *
FROM f
UNION ALL
 SELECT null, null, (SELECT sum(bidprice) FROM f), null, null;

對於您的查詢，第一種方法如下所示..

SELECT Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate, sum(bidPrice)
FROM Bid
INNER JOIN Item ON bid.ItemId=Item.ItemId
WHERE BidDate between '2016-08-24' and '2017-11-15'
GROUP BY GROUPING SETS ( (Item.ItemId, Item.ItemDescription, Bid.BidPrice, bid.BidDate), () )

引用自：https://dba.stackexchange.com/questions/192259