SQL query: how to create subtotal rows when there is no aggregate function
One option is Grouping Sets
Example
Declare @YourTable Table ([Date] varchar(50),[INVNUNBER] varchar(50),[CUSTOMER] varchar(50),[ITEM] varchar(50),[QTY] int,[SALES] int)
Insert Into @YourTable Values
(20190630,'IN3343','joe''s comp',23225,2.0,3000)
,(20190630,'IN3343','joe''s comp',23214,1.0,400)
,(20190630,'IN3353','matt''s comp.',12222,3.0,6000)
,(20190630,'IN3353','matt''s comp.',32222,3.0,3000)
Select Date
,InvNunber
,Customer
,Item
,Qty = sum(Qty)
,Sales = sum(Sales)
From @YourTable
Group By
Grouping Sets (
(Date,InvNunber,Customer,Item)
,(Date,InvNunber)
,(left(Date,0))
)
Order By left(Date,0) Desc
,Date
,InvNunber
,Customer Desc
Returns
Date InvNunber Customer Item Qty Sales
20190630 IN3343 joe's comp 23214 1 400
20190630 IN3343 joe's comp 23225 2 3000
20190630 IN3343 NULL NULL 3 3400
20190630 IN3353 matt's comp. 12222 3 6000
20190630 IN3353 matt's comp. 32222 3 3000
20190630 IN3353 NULL NULL 6 9000
NULL NULL NULL NULL 9 12400
How To Get Running Subtotal with Group By in SQL Server
OLAP functions are calculated after aggregation, you can't use ActualAmount, must be SUM( ActualAmount). And there's no need to order by ProjectId because it's already in PARTITION BY. Finally use ROWS UNBOUNDED PRECEDING otherwise it defaults to RANGE UNBOUNDED PRECEDING which is more expensive and might not return the expected result:
SELECT [ProjectId]
, [YearQuarter]
, SUM( ActualAmount) AS PeriodAmount
, SUM( SUM( ActualAmount))
OVER (PARTITION BY ProjectId
ORDER BY YearQuarter
ROWS UNBOUNDED PRECEDING) AS FairMarketValue
FROM GLSnapshot
GROUP BY [ProjectId] , [YearQuarter]
SQL How to create output with sub totals
If this was about aggregation without pivoting, you could use a CASE expression, like this:
SELECT
...
Local_1 = SUM(CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END),
...
FROM ...
GROUP BY ...
However, in the PIVOT clause, the argument of the aggregate function must be just a column reference, not an expression. You can work around that by transforming the original dataset so that QTY is either positive or negative, depending on Transaction:
SELECT
PubID,
QTY = CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END,
LocationID
FROM dbo.InventoryLog
The above query will give you a result set like this:
PubID QTY LocationID
----- --- ----------
1 10 1
1 20 2
1 30 3
1 -5 1
1 -10 2
1 -5 3
2 10 1
2 10 2
2 -5 2
2 -8 2
1 20 1
1 20 2
2 -2 2
which is now easy to pivot:
WITH prepared AS (
SELECT
PubID,
QTY = CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END,
LocationID
FROM dbo.InventoryLog
)
SELECT
PubID,
Local_1 = [1],
Local_2 = [2],
Local_3 = [3]
FROM prepared
PIVOT
(
SUM(QTY)
FOR LocationID IN ([1], [2], [3])
) AS p
;
Note that you could actually prepare the names Local_1, Local_2, Local_3 beforehand and avoid renaming them in the main SELECT. Assuming they are formed by appending the LocationID value to the string Local_, here's an example of what I mean:
WITH prepared AS (
SELECT
PubID,
QTY = CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END,
Name = 'Local_' + CAST(LocationID AS varchar(10))
FROM dbo.InventoryLog
)
SELECT
PubID,
Local_1,
Local_2,
Local_3
FROM prepared
PIVOT
(
SUM(QTY)
FOR Name IN (Local_1, Local_2, Local_3)
) AS p
;You will see, however, that in this solution renaming will be needed at some point anyway, so I'll use the previous version in my further explanation.
Now, adding the totals to the pivot results as in your desired output may seem a little tricky. Obviously, the column could be calculated simply as the sum of all the Local_* columns, which might actually not be too bad with a small number of locations:
WITH prepared AS (
SELECT
PubID,
QTY = CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END,
LocationID
FROM dbo.InventoryLog
)
SELECT
PubID,
Local_1 = [1],
Local_2 = [2],
Local_3 = [3]
Total = COALESCE([1], 0)
+ COALESCE([2], 0)
+ COALESCE([3], 0)
FROM prepared
PIVOT
(
SUM(QTY)
FOR LocationID IN ([1], [2], [3])
) AS p
;(COALESCE is needed because some results may be NULL.)
But there's an alternative to that, where you don't have to list all the locations explicitly one extra time. You could return the totals per PubID alongside the details in the prepared dataset using SUM() OVER (...), like this:
WITH prepared AS (
SELECT
PubID,
QTY = CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END,
LocationID,
Total = SUM(CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END)
OVER (PARTITION BY PubID)
FROM dbo.InventoryLog
)
…or like this, if you wish to avoid repetition of the CASE expression:
WITH prepared AS (
SELECT
t.PubID,
QTY = x.AdjustedQTY,
t.LocationID,
Total = SUM(x.AdjustedQTY) OVER (PARTITION BY t.PubID)
FROM dbo.InventoryLog AS t
CROSS APPLY (
SELECT CASE t.[Transaction] WHEN 'Add' THEN t.QTY ELSE -t.QTY END
) AS x (AdjustedQTY)
)
…Then you would just include the Total column into the main SELECT clause along with the pivoted results and PubID:
…
SELECT
PubID,
Local_1,
Local_2,
Local_3,
Total
FROM prepared
PIVOT
(
SUM(QTY)
FOR LocationID IN ([1], [2], [3])
) AS p
;That would be the total column for you. As for the row, it is actually easy to add it when you are acquainted with the ROLLUP() grouping function:
…
SELECT
PubID,
Local_1 = SUM([1]),
Local_2 = SUM([2]),
Local_3 = SUM([3]),
Total = SUM(Total)
FROM prepared
PIVOT
(
SUM(QTY)
FOR LocationID IN ([1], [2], [3])
) AS p
GROUP BY ROLLUP(PubID)
;The total row will have NULL in the PubID column, so you'll again need COALESCE to put the word Total instead (only if you want to return it in SQL; alternatively you could substitute it in the calling application):
…
PubID = COALESCE(CAST(PubID AS varchar(10)), 'Total'),
…
And that would be all. To sum it up, here is a complete query:
WITH prepared AS (
SELECT
PubID,
QTY = x.AdjustedQTY,
t.LocationID,
Total = SUM(x.AdjustedQTY) OVER (PARTITION BY t.PubID)
FROM dbo.InventoryLog AS t
CROSS APPLY (
SELECT CASE t.[Transaction] WHEN 'Add' THEN t.QTY ELSE -t.QTY END
) AS x (AdjustedQTY)
)
SELECT
PubID = COALESCE(CAST(PubID AS varchar(10)), 'Total'),
Local_1 = SUM([1]),
Local_2 = SUM([2]),
Local_3 = SUM([3]),
Total = SUM(Total)
FROM prepared
PIVOT
(
SUM(QTY)
FOR LocationID IN ([1], [2], [3])
) AS p
GROUP BY ROLLUP(PubID)
;
As a final touch to it, you may want to apply COALESCE to the SUMs as well, to avoid returning NULLs in your data (if that is necessary).
SQL query to get the subtotal of some rows
You can aggregate using a CASE expression, which forms a group using the id if the manager_id be zero, otherwise using the manager_id. The rest of the logic is similar to what you already have.
SELECT
CASE WHEN manager_id = 0 THEN id ELSE manager_id END AS manager_id,
MAX(CASE WHEN is_manager=1 THEN name END) AS name,
SUM(no_of_items) AS total_items,
SUM(revenue) AS total_revenue
FROM items_revenue
GROUP BY
CASE WHEN manager_id = 0 THEN id ELSE manager_id END;
Demo
One side note: I used a function in the GROUP BY clause, which is not ANSI compliant and therefore may not run everywhere. To fix this, we can first subquery your table to generate the effective manager groups. Then, use my above answer against this intermediate result.
Add a subtotal column for aggregated columns
You seem to want:
SELECT OT.TRADER_ID, OT.TRADER_NAME, OT.CCP,
COUNT(*) OVER (PARTITION BY OT.TRADER_ID) as NUM_CCP,
SUM(OT.TRADED_AMT) AS TRADED_AMT_WITH_CCP,
SUM(SUM(OT.TRADED_AMT)) OVER (PARTITION BY OT.TRADER_ID) AS VALUE_OF_TOTAL_TRADES,
COUNT(OT.TRADE_ID) AS CCP_TRADES,
SUM(COUNT(OT.TRADE_ID)) OVER (PARTITION BY OT.TRADER_ID) AS TOTAL_TRADES
FROM ORDERS_TRADES OT
GROUP BY OT.TRADER_ID, OT.TRADER_NAME, OT.CCP;
I'm not sure what your query has to do with the results you want. The columns have little to do with what you are asking.
Here is a db<>fiddle.
SQL SERVER T-SQL Calculate SubTotal and Total by group
I think this is what you want:
select (case when GROUPING(CustomerName) = 0 and
GROUPING(Employee) = 1 and
GROUPING(DocDate) = 1 and
GROUPING(LegalID) = 1
then 'Total ' + CustomerName
when GROUPING(CustomerName) = 1 and
GROUPING(Employee) = 1 and
GROUPING(DocDate) =1 and
GROUPING(LegalID) = 1 then 'Total'
else CustomerName
end) as CustomerName,
LegalID, Employee,DocDate,
sum(DocTotal) as DocTotal,
sum(DueTotal) as DueTotal
From @Sales
group by grouping sets((LegalID, CustomerName ,Employee, DocDate),
(CustomerName),
()
);
SQL Subtotal and Grand Totals
Try this:
SELECT
o.OrderID, c.ContactName, o.OrderDate, SUM(od.Quantity * od.UnitPrice) AS grand_total
FROM
dbo.Customers c
INNER JOIN
dbo.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN
dbo.[Order Details] od ON o.OrderID = od.OrderID
WHERE
o.OrderDate BETWEEN {ts '1996-07-01 00:00:00.000'} AND {ts '1996-07-31 23:59:59.997'}
GROUP BY
GROUPING SETS ((o.OrderID, c.ContactName, o.OrderDate), ())
GROUPING SETS ((o.OrderID, c.ContactName, o.OrderDate), ...) will group the source rows by o.OrderID, c.ContactName, o.OrderDate and, also, will compute the SUM for every OrderID, ContactName, OrderDate pair of values.
GROUPING SETS ((...), ()) instructs SQL Server to compute, also, the grand total (total of all order totals):
SELECT OrderID, SUM(OrderDetailValue) AS OrderTotal
FROM (
SELECT 11, 1, 'A', 1 UNION ALL
SELECT 12, 1, 'B', 10 UNION ALL
SELECT 13, 2, 'A', 100
) AS Orders(OrderDetailID, OrderID, ProductName, OrderDetailValue)
GROUP BY GROUPING SETS ((OrderID), ());
Results:
OrderID OrderTotal
------- ----------
1 11 <-- Total generated by GROUPING SETS ((OrderID), ...)
2 100 <-- Total generated by GROUPING SETS ((OrderID), ...)
NULL 111 <-- Total generated by GROUPING SETS (..., ())
Grouping sets: display subtotals in other specific column?
I agree with Jamie you may want the subtotals visually handled in a different layer, but what you might want to try is using the GROUPING() function on the column. This function returns 1 if it is part of the GROUPING SETS subtotals, and 0 if it is a regular column. http://technet.microsoft.com/en-us/library/ms178544(SQL.90).aspx
I included the sample data I tested with. Remove the first WITH emp_test AS () when you use the select statement.
My Test Data:
WITH emp_test AS
(
SELECT 10 AS DEPTNO, 7782 AS EMPNO, 20000 AS sal
UNION ALL SELECT 10, 7839, 10000
UNION ALL SELECT 20, 7566, 5950
UNION ALL SELECT 20, 7788, 6000
)
Answer to get Subtotals on separate column:
SELECT deptno, empno
, CASE
WHEN GROUPING(empNo) = 1 THEN null
ELSE SUM(sal)
END as sum_salary
, CASE
WHEN GROUPING(empno) = 1 THEN SUM(sal)
ELSE NULL
END AS SubTotal
FROM emp_test
GROUP BY GROUPING SETS (DeptNo, Empno), (DeptNo)
Calculating Subtotals from SQL Query
Your query is pretty elaborate and the output seems to be for a report, couldn't you just elaborate your sub-total in the report? (SSRS Tutorial for groups and totals)
If it is not possible, i think you could modify your stored procedure to use a Table Variable: load the query in the table and then run the various subtotal query on it.
Related Topics
How to Test My Ad-Hoc SQL with Parameters in Postgres Query Window
Listagg Alternative in Oracle 10G
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
Select Query Does Not Work When Converted to Vba - Invalid SQL Statement
Advisory Locks or Nowait to Avoid Waiting for Locked Rows
SQL Find Sets with Common Members (Relational Division)
Using Table Just After Creating It: Object Does Not Exist
Orderby in SQL Server to Put Positive Values Before Negative Values
Listagg Query "Ora-00937: Not a Single-Group Group Function"
Join Tables on Nearest Date in the Past, in MySQL
Join/Pivot Items with Eav Table
Oracle-Xmltype:How to Update a Value
How to Escape Double Quotes Inside a SQL Fulltext 'Contains' Function
How to Query Named Range on Sheet with Spaces in Name in Excel