SQL Pivot Table Subtotal Syntax error

Question

Hoping someone can help me. I was able to put together this SQL script but it coming back with minor errors here and there. I'be been trying to debug for over a week now. Please help. the first error message is "Incorrect syntax near ')'." If you fixe that it keeps on throwing more out so I'm thinking I'm not coding this correctly. I am unable to save changes that do work. it tell me the save request was aborted. Working with SQL Server 2008 r2

SELECT     
PublicationID AS PubID, (PubNum + '-  ' + PubTitle) AS [Pub Descr], 
CONVERT(Varchar(10), [Datestamp], 101) AS [Date Printed], 
QtyPrinted AS [Qty Printed], 
[2] AS [Tyler Inventory], 
[1] AS [Central Inventory], 
[3] AS [Mailing House Inventory],

(
SELECT     SUM(S)
FROM  
(
SELECT [1] UNION ALL
SELECT [2] UNION ALL
SELECT [3]
) AS T (S)) AS [Current Inventory], 
RecycledQty AS [Recycled], 
MailingVendorName AS [Mailing Vendor], 
PrintVendorName AS [Print Vendor]

FROM
(
SELECT 
PublicationID, LocationID, Balance, PubNum, 
PubTitle, ItemPerCase, Datestamp, Deleted,     
RecycledQty, MailingVendorName, PrintVendorName, 
QtyPrinted

FROM          
(
dbo.view_PubInventory_Main_Summary_RAW) x PIVOT (sum(balance) FOR 
LocationID IN ([1],   [2], [3])) p)
    SELECT     *
     FROM  
     (SELECT   PUBID, [Pub Descr], [Date Printed], [Qty Printed], 
      [Tyler Inventory], [Central Inventory], 
      [Mailing House Inventory], [Current Inventory], [Recycled],
      [Mailing Vendor]
      FROM GG
      ) AS T

I used the following two post to help me build my own query. http://stackoverflow.com/questions/7925748/can-you-subtotal-rows-and-or-columns-in-a-pivot-table — Russell Peters, Mar 17 '14 at 20:17
How many queries do you intend this to be? I see 2 distinct queries. What do you intend to do with the sub-select in the top query? The queries are malformed, no question, but some additional insight as to what you're trying to do might be helpful. — Hart CO, Mar 17 '14 at 21:06
The main idea is to list the inventory levels of each location in columns on a report with a grand total inventory at the far right column of the report. I accidently added extra syntax to this query and I cannot seem to debug it. The query was working property when it was saved to SQL Server as a view but whenever I tried to save the SQL view, it tells me incorrect syntax and wont save. So what you see is a Pivot table with a grand total for each inventory at each location and a grand total. — Russell Peters, Mar 17 '14 at 22:14

score 0 · Answer 1 · answered Mar 17 '14 at 21:11

Hard to follow exactly what you're after, but I think you want Current Inventory to just be [1]+[2]+[3], and you didn't alias your subquery. The query at the bottom looks fine.

 SELECT PublicationID AS PubID
      , PubNum + '-  ' + PubTitle AS [Pub Descr]
      , CONVERT(VARCHAR(10), [Datestamp], 101) AS [Date Printed]
      , QtyPrinted AS [Qty Printed]
      , [2] AS [Tyler Inventory]
      , [1] AS [Central Inventory]
      , [3] AS [Mailing House Inventory]
      , [1]+[2]+[3] AS [Current Inventory]
      , RecycledQty AS [Recycled]
      , MailingVendorName AS [Mailing Vendor]
      , PrintVendorName AS [Print Vendor]
 FROM   ( SELECT    PublicationID
                  , LocationID
                  , Balance
                  , PubNum
                  , PubTitle
                  , ItemPerCase
                  , Datestamp
                  , Deleted
                  , RecycledQty
                  , MailingVendorName
                  , PrintVendorName
                  , QtyPrinted
           FROM     dbo.view_PubInventory_Main_Summary_RAW
           PIVOT ( SUM(balance) FOR LocationID IN ( [1], [2], [3] ) ) p
        )AS Sub

SQL Pivot Table Subtotal Syntax error

1 Answers1

Linked