CODEWITHSUNDEEP
sql
User88
User88

Reputation: 3

How to Sum Total in Header Detail Tables

Please, I need your help, I'm new to the subject, so I have tow tables Order table and Order Detail table. I want to sum order amount in the order table and group by the product id exists in the order detail table. I'm getting repeated rows.

select p.productName 
       SUM(o.Amount - o.discount) as OrdersTotal
  FROM dbo.order o
       inner join
       OrderDetail d
    ON o.orderid = d.orderid
       inner join
       dbo.product p
    ON d.productid = p.productid
    where o.orderdate >= @fromdate
      and o.orderdate <= @todate
group by p.productname, o.discount

the result I'm getting is as follow

product name    orders total
------------    ------------
product A       150
product A       20
product B       45
product B       13

so please, how can I fix this result? Thanks

Upvotes: 0

Views: 487

Answers (1)

R Pelzer
R Pelzer

Reputation: 1278

You should only group on the product name, so that all records with the same product name are included in the sum of the amount:

select p.productName 
       SUM(o.Amount) as OrdersTotal
  FROM dbo.order o
       inner join
       OrderDetail d
    ON o.orderid = d.orderid
       inner join
       dbo.product p
    ON d.productid = p.productid
    where o.orderdate >= @fromdate
      and o.orderdate <= @todate
group by p.productname

Upvotes: 1

Related Questions