CODEWITHSUNDEEP
sqlsql-servert-sql
scava91
scava91

Reputation: 3

how i can avoid duplicate values in grouping?

I'd like to indicate every single Company's total amount (money) of orders placed.

PS: totalamount = quantity*unitprice

select  Customers.CompanyName, ([Order Details].OrderID), sum([Order Details].Quantity*[Order Details].UnitPrice)
from Customers
 join orders
on customers.CustomerID=customers.CustomerID
 join [Order Details]
on orders.OrderID=[Order Details].OrderID
group by Customers.CompanyName, [Order Details].OrderID
order by customers.CompanyName asc

Code above lists the rows according to the OrderID and duplicates the CompanyName

On the other hand, code below doesn't duplicate the CompanyName but nevertheless it shows the amount as combined all the orders placed in [Order Details]

select  Customers.CompanyName, sum([Order Details].Quantity*[Order Details].UnitPrice)
from Customers
 join orders
on customers.CustomerID=customers.CustomerID
 join [Order Details]
on orders.OrderID=[Order Details].OrderID
group by Customers.CompanyName
order by customers.CompanyName asc

My second query does this:

Alfreds Futterkiste 10643   1086,00
Alfreds Futterkiste 10692   878,00
Alfreds Futterkiste 10702   330,00
Alfreds Futterkiste 10835   851,00
Alfreds Futterkiste 10952   491,20
Alfreds Futterkiste 11011   960,00
Ana Trujillo Emparedados y helados  10308   88,80
Ana Trujillo Emparedados y helados  10625   479,75
Ana Trujillo Emparedados y helados  10759   320,00
Ana Trujillo Emparedados y helados  10926   514,40

But I want this:

Alfreds Futterkiste 3636,00
Ana Trujillo Emparedados y helados 1402,95

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think you just want an aggregation query:

select c.CompanyName,
       sum(od.Quantity * od.UnitPrice) as totalamount
from Customers c join
     orders o
     on c.CustomerID = o.CustomerID join
     [Order Details] od
     on o.OrderID = od.OrderID
group by c.CompanyName
order by c.CompanyName asc;

If you want one row per CompanyName in the result set, then that should be the only column in the GROUP BY.

Also note the use of table aliases. These make the query easier to write and to read.

Upvotes: 1

Related Questions