CODEWITHSUNDEEP
sql-server
zanhtet
zanhtet

Reputation: 2050

How to get total sum

I have two tables.

Sales
------

ID    Charge    VAT
1     100      10
2     200      20


SaleProducts
------------

ID  Product    Auto   SalesID
1   aa         True   1 
2   bb         False  1

I want to get this

SaleOnProduct
-------------

ID    Product     Charge     VAT    Total   TotalAmount(All of total is plus)
1     aa          100        10     110     220  
2     aa          100        10     110     220

How can I do this. Please help me.

Upvotes: 1

Views: 2716

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @Sales table (ID int, Charge int, VAT int)
declare @SaleProducts table (ID int, Product char(2), Auto bit, SalesID int)

insert into @Sales values
(1, 100, 10),
(2, 200, 20)

insert into @SaleProducts values
(1, 'aa', 1, 1),
(2, 'bb', 0, 1)

select 
  SP.ID, 
  SP.Product,
  S.Charge,
  S.VAT,
  S.Charge+S.VAT as Total,
  sum(S.Charge+S.VAT) over() as TotalAmount
from @Sales as S
  inner join @SaleProducts as SP
    on S.ID = SP.SalesID

Upvotes: 2

pcofre
pcofre

Reputation: 4066

This query do the job: (I supposed SQL Server 2005 or above, otherwise you´ve to change the cte by a temp table)

WITH ReportCte as(
SELECT  b.Id IdSale,
        a.Id IdProduct,
        a.Product,
        b.Charge,
        b.VAT,
        b.Charge+b.VAT Total
  FROM  [dbo].[SaleProducts] a left join
        [dbo].[Sales] b on a.[SalesID] = b.ID)
SELECT  a.IdProduct,
        a.IdProduct,
        a.Charge,
        a.VAT,
        a.Total,
        b.TotalAmount
FROM    ReportCte a left join
        (select IdSale,SUM(Total) TotalAmount 
        from ReportCte group by IdSale) b on a.IdSale=b.IdSale

Upvotes: 0

Simen S
Simen S

Reputation: 3205

In order to get data from both tables in the same resultset you need to do a join.

Since you want to have a summary row for all sales ((charge+VAT)*numberofSaleProductsRows) of each particular product, you need to use the SUM aggregate funciton, and a GROUP BY clause. All the columns which you need in your resultset and which do not have a specified aggregation needs to be included in the GROUP BY list.

Disclaimer: Untested code

 SELECT ID, Product, Charge ,VAT, Charge + VAT as Total, 
            Sum(Charge + VAT) as TotalAmount
 FROM Sales INNER JOIN SaleProducts 
           ON Sales.ID = SaleProducts.SalesID
 GROUP BY ID, Product, Charge, VAT, Charge + VAT

Upvotes: 0

despina
despina

Reputation: 437

select *, (charge+VAT) as total, SUM(charge+VAT) as totalAmount
from sales
join saleproducts on sales.ID=saleproducts.salesID
group by sales.ID

Upvotes: -1

Related Questions