CODEWITHSUNDEEP
sqlsql-server
Manish Sharma
Manish Sharma

Reputation: 2426

How to select only customer data and sum of that at bottom of table as row using sql query?

i have sql query. this query is providing me a lots of record of customer and that voucher and order subtotal but i want sum of debit and credit row and credit and debit column sum at bottom position of table.

so guide me how can i achieve this table using by query.

this is my sql query.

declare @Credit decimal(18,2)
declare @Debit decimal(18,2) 

select @debit= SUM(sd.SubTotal) 
from SalesOrderDetails sd 
left join SalesOrder so 
    on sd.SalesOrderId = so.SalesOrderId 

select @credit = SUM(Amount) from Vouchers 

SELECT CustomerId,
    OrderDate, 
    VoucherType, 
    VoucherNo, 
    SUM(Debit) as Debit, 
    SUM(Credit) as Credit
FROM (SELECT so.CustomerId, 
            so.OrderDate ,
            'Sales' As VoucherType, 
            '' AS VoucherNo,
            a.Total As Debit
            , NULL As Credit
       from SalesOrder so 
       inner join (
                   SELECT SalesOrderId,
                          sum(SubTotal) AS Total
                   FROM SalesOrderDetails             
                   group by SalesOrderId
                  )as a 
            on so.SalesOrderId = a.SalesOrderId
        UNION ALL
        SELECT V.CustomerId
               ,v.VoucherDate As OrderDate
               , 'Receipt' AS VoucherType
               ,v.VoucherNumber AS VoucherNo
               ,NULL AS Debit
               ,v.Amount AS Credit
         from Vouchers v 
       ) ledger
GROUP BY GROUPING SETS ((CustomerId),(OrderDate, VoucherType, VoucherNo), ())

i have attached my output image. enter image description here

in my image i got this result but that is very messy process so give me a proper solution for this.

Upvotes: 0

Views: 623

Answers (1)

sarin
sarin

Reputation: 5307

Add a HAVING Clause which happens after the grouping and aggregating?

....
GROUP BY GROUPING SETS ((CustomerId),(OrderDate, VoucherType, VoucherNo), ())
HAVING VoucherType Is Null

Upvotes: 1

Related Questions