SQL How to create output with sub totals

Question

I'm new to T-SQL and need help converting an excel report to a run on SQL. I have a SQL table that records all the daily inventory transactions (in/out) from each stockroom. I need to create a report that list the current inventory levels for each product in each location and the qty in each place as follows. In other words, the current inventory levels of each place.

I also need help on how to insert the Preferred Out Report (below) into SQL Server as a view so I can run this each month over and over again.

Thanks in Advance!

Inventory Log table:

PubID   QTY LocationID  Transaction
1       10  1           Add
1       20  2           Add
1       30  3           Add
1       5   1           Sold
1       10  2           Sold
1       5   3           Sold
2       10  1           Add
2       10  2           Add
2       5   2           Sold
2       8   2           Sold
1       20  1           Add
1       20  2           Add
2       2   2           Sold

Preferred Output Table:

PubID   Local_1 Local_2 Local_3 Total
1       25      30      25      80
2       5       0       0       5
Total   30      30      25      85

I see a lot of close examples here but most just add the value while I need to subtract the Sold inventory from the Added stock to get my totals in each column.

The row totals and column totals on the right and bottom are pluses but not needed if it's easier without.

THANKS!

possible duplicate of [SQL Server pivot dynamic columns, no aggregation](http://stackoverflow.com/questions/11985796/sql-server-pivot-dynamic-columns-no-aggregation) — M.Ali, Feb 27 '14 at 21:07
I think this question is different enough to warrant a new post. I seen a lot of close examples but none that does this. I'm open to other post if you see more that is asking what I am. THANKS — Russell Peters, Feb 27 '14 at 22:58

score 1 · Accepted Answer · answered Mar 04 '14 at 20:04

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).

score 0 · Answer 2 · answered Feb 28 '14 at 13:40

The query below does what you need. I might have had one extra group by that could be combined into 1 but you get the idea.

DECLARE  @InventoryLog TABLE
(
    PubId INT,
    Qty INT,
    LocationId INT,
    [Transaction] Varchar(4)

)

DECLARE  @LocationTable TABLE
(
    Id INT,
    Name VarChar(10)

)

INSERT INTO @LocationTable
VALUES
(1, 'LOC_1'),
(2, 'LOC_2'),
(3, 'LOC_3')

INSERT INTO @InventoryLog
VALUES 
(1 ,      10,  1 ,          'Add'),
(1 ,      20,  2 ,          'Add'),
(1 ,      30,  3 ,          'Add'),
(1 ,      5 ,  1 ,          'Sold'),
(1 ,      10,  2 ,          'Sold'),
(1 ,      5 ,  3 ,          'Sold'),
(2 ,      10,  1 ,          'Add'),
(2 ,      10,  2 ,          'Add'),
(2 ,      5 ,  2 ,          'Sold'),
(2 ,      8 ,  2 ,          'Sold'),
(1 ,      20,  1 ,          'Add'),
(1 ,      20,  2 ,          'Add'),
(2 ,      2 ,  2 ,          'Sold')

SELECT PubId, 
       lT.Name LocationName, 
       CASE
            WHEN [Transaction] ='Add' Then Qty
            WHEN [Transaction] ='Sold' Then -Qty
       END as Quantity
INTO   #TempInventoryTable
FROM @InventoryLog iL
INNER JOIN @LocationTable  lT on iL.LocationId = lT.Id

SELECT * INTO #AlmostThere
FROM
(
SELECT PubId, 
       ISNULL(LOC_1,0) LOC_1,
       ISNULL(LOC_2,0) LOC_2, 
       ISNULL(LOC_3,0) LOC_3,
       SUM(ISNULL(LOC_1,0) + ISNULL(LOC_2,0) + ISNULL(LOC_3,0)) AS TOTAL
FROM #TempInventoryTable s
PIVOT 
(
    SUM(Quantity)
    FOR LocationName in (LOC_1,LOC_2,LOC_3)
) as b
GROUP BY PubId, LOC_1, LOC_2, LOC_3
) b

SELECT CAST(PubId as VARCHAR(10))PubId,
       LOC_1,
       LOC_2,
       LOC_3,
       TOTAL
FROM #AlmostThere
UNION
SELECT ISNULL(CAST(PubId AS VARCHAR(10)),'TOTAL')  PubId, 
       [LOC_1]= SUM(LOC_1),
       [LOC_2]= SUM(LOC_2),
       [LOC_3]= SUM(LOC_3),
       [TOTAL]= SUM(TOTAL)
FROM #AlmostThere
GROUP BY ROLLUP(PubId)




DROP TABLE #TempInventoryTable
DROP TABLE #AlmostThere



 PubId  LOC_1   LOC_2   LOC_3   TOTAL 

   1     25  30  25  80 

   2     10  -5   0   5

 TOTAL   35  25  25  85

Sql Fiddle

score 0 · Answer 3 · answered Mar 04 '14 at 20:06

Here is another approach: aggregate the data before pivoting, then pivot the aggregated results.

Compared to my other suggestion, this method is much simpler syntactically, which may also make it easier to understand and maintain.

All the aggregation is done with the help of the CUBE() grouping function. The basic query would be this:

SELECT
  PubID,
  LocationID,
  QTY = SUM(CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END)
FROM dbo.InventoryLog
GROUP BY CUBE(PubID, LocationID)

You can see the same CASE expression as in my other answer, only this time it can be directly used as the argument of SUM.

Using aggregation by CUBE gives us not only the totals by (PubID, LocationID), but also by PubID and LocationID separately, as well as the grand total. This is the result of the query for the example in your question:

PubID  LocationID  QTY
-----  ----------  ---
1      1           35
2      1           10
NULL   1           45
1      2           50
2      2           25
NULL   2           75
1      3           35
NULL   3           35
NULL   NULL        155
1      NULL        120
2      NULL        35

Rows with NULLs in LocationID are row totals in the final result set, and those with NULLs in PubID are column totals. The row with NULLs in both columns is the grand total.

Before we can proceed with the pivoting, we need to prepare column names for the pivoted results. If the names are supposed to be derived from the values of LocationID, the following declaration will replace LocationID in the original query's SELECT clause:

Location = COALESCE('Local_' + CAST(LocationID AS varchar(10)), 'Total')

We can also substitute 'Total' for the NULLs in PubID at this same stage, so this will replace PubID in the SELECT clause:

PubID = COALESCE(CAST(PubID AS varchar(10)), 'Total')

Now the results will look like this:

PubID  LocationID  QTY
-----  ----------  ---
1      Local_1     35
2      Local_1     10
Total  Local_1     45
1      Local_2     50
2      Local_2     25
Total  Local_2     75
1      Local_3     35
Total  Local_3     35
Total  Total       155
1      Total       120
2      Total       35

and at this point everything is ready to apply PIVOT. This query transforms the above result set according to the desired format:

WITH aggregated AS (
  SELECT
    PubID    = COALESCE(CAST(PubID AS varchar(10)), 'Total'),
    Location = COALESCE('Local_' + CAST(LocationID AS varchar(10)), 'Total'),
    QTY      = SUM(CASE [Transaction] WHEN 'Add' THEN QTY ELSE -QTY END)
  FROM dbo.InventoryLog
  GROUP BY CUBE(PubID, LocationID)
)
SELECT
  PubID,
  Local_1,
  Local_2,
  Local_3,
  Total
FROM aggregated
PIVOT (
  MAX(QTY)
  FOR Location IN (Local_1, Local_2, Local_3, Total)
) AS p
;

This query will return NULLs for missing combinations of (PubID, LocationID). If you want to return 0 instead, apply COALESCE to the result of SUM in the definition of aggregated.

SQL How to create output with sub totals

3 Answers3