locked
SubTotal & GrandTotal Of Quantity & Amount RRS feed

  • Question

  • User1979860870 posted

    Hi

       In below query i want to display Subtotal & Grand Total using Rollup 

    SELECT 
    T0.DocNo,T0.Date,T1.Item,T1."Quantity" ,
    T1.Price,T1.Amount
    
    FROM Tbl1 T0  INNER JOIN Tbl2 T1 ON T0."DocNo" = T1."DocNo" 

    Thanks

    Thursday, December 31, 2020 4:06 PM

All replies

  • User475983607 posted

    Hi

       In below query i want to display Subtotal & Grand Total using Rollup 

    SELECT 
    T0.DocNo,T0.Date,T1.Item,T1."Quantity" ,
    T1.Price,T1.Amount
    
    FROM Tbl1 T0  INNER JOIN Tbl2 T1 ON T0."DocNo" = T1."DocNo" 

    When asking for assistance on the forum it is recommend that you populate a temp table with sample data and at least try to write the code.  If you take a few moments to read the ROLLUP docs, you'll find sample code. 

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15#group-by-rollup

    There's also Google.

    https://www.google.com/search?q=sql+rollup

    Thursday, December 31, 2020 4:13 PM
  • User1535942433 posted

    Hi jagjit saini,

    As far as I think,you couldn't use group Rollup.You need to use grouping sets.

    SELECT a.DocNo, a.[Date],b.Item,b.Quantity,b.price,b.Amount,sum(Amount*Price) AS total FROM tbl1 a
    JOIN tbl2 b ON a.DocNo = b.DocNo
    GROUP BY grouping  sets ((a.DocNo, a.[Date],b.Item,b.Quantity,b.Amount,b.price),())
    

    Result:

    Best regards,

    Yijing Sun

    Friday, January 1, 2021 2:51 AM