SQL Server – Adding Total Sum Below Column of Prices

aggregatesql serversql-server-2012

I am running this select query on my database:

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'

I am getting the below result:

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

My question is: Is it possible that instead of the Total Bids column with a total at each row I just get one total at the bottom of the BidPrice Column?

Best Answer

You can use a GROUP BY GROUPING SETS over the NULL set

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)

Verified to work with PostgreSQL. SQL Server 2014 and 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 );

The docs at PostgreSQL are much better explaining this

Another method of doing this is with a UNION,

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

For your query, the first method would look like this..

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), () )

Related Solutions

SQL Server – Compute the Total of SUM(Column)

CREATE TABLE #foo
(
 itemid int, 
 proddte date,
 qty int
);

INSERT #foo(itemid,proddte,qty) VALUES
(1,'20140101',5),(1,'20140102',7),(2,'20150101',10);


-- if it really needs to be a column with the same value
-- in every row, just calculate once and assign it to a variable

DECLARE @sum int = (SELECT SUM(qty) FROM #foo);

SELECT itemid, proddte, GroupedSum = SUM(qty), GrandTotal = @sum
  FROM #foo
  GROUP BY itemid, proddte;

-- if the grand total can be expressed on its own row, 
-- you can use GROUP BY GROUPING SETS:
SELECT itemid, proddte, SUM(qty)
  FROM #foo GROUP BY GROUPING SETS((),(itemid,proddte));

-- if that syntax is confusing, you can use a less
-- efficient UNION ALL:
SELECT itemid, proddte, SUM(qty)
  FROM #foo GROUP BY itemid,proddte
UNION ALL
SELECT NULL, NULL, SUM(qty) 
  FROM #foo;

GO
DROP TABLE #foo;

The GROUP BY GROUPING SETS is is basically a UNION ALL. The () means just take the SUM regardless of grouping, any other group listed gets aggregated separately. Try GROUP BY GROUPING SETS ((itemid),(itemid,proddte)) to see the difference.

For more details see the documentation:

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

As Andriy mentioned, the query above could also be written using:

GROUP BY ROLLUP( (itemid,proddte) )

Note the two columns there are enclosed in an additional pair of parentheses, making them a single unit. Andriy wrote a demo hosted on the Stack Exchange Data Explorer.

SQL Server Query Refinement – How to Optimize T-SQL Queries

Add as many SUM(CASE as you need according selected dates.

SELECT      WoPart.PartNumber AS [Part Number]
           ,Parts.Descript AS [Description]
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/14'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2014 THEN Qty ELSE 0 END) AS '2014'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/15'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2015 THEN Qty ELSE 0 END) AS '2015'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 1 THEN Qty ELSE 0 END) AS 'Qtr1/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 2 THEN Qty ELSE 0 END) AS 'Qtr2/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 3 THEN Qty ELSE 0 END) AS 'Qtr3/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 
                      AND DATEPART(QQ, RelDateTime) = 4 THEN Qty ELSE 0 END) AS 'Qtr4/16'
           ,SUM(CASE WHEN YEAR(RelDateTime) = 2016 THEN Qty ELSE 0 END) AS '2016'
FROM       WoPart
INNER JOIN WorkOrders
ON         WorkOrders.RecordID = WoPart.RecordID
INNER JOIN Parts
ON         WoPart.PartNumber = Parts.PartNumber
WHERE      RelDateTime >= CAST('20140101' AS DATETIME)
AND        RelDateTime <  CAST('20170101' AS DATETIME)
GROUP BY   WoPart.PartNumber, Parts.Descript;

Best Answer

Related Solutions

SQL Server – Compute the Total of SUM(Column)

SQL Server Query Refinement – How to Optimize T-SQL Queries

Related Question