CODEWITHSUNDEEP
sql-servert-sql
fahmijaafar
fahmijaafar

Reputation: 185

How to INNER JOIN with SUM

I'm trying to join two tables, one with sales and one with profile. both table has same customer No but sales table didn't have customer name. How do I inner join them so that I can get top 10 highest sum amount sales of customers, but I want to show their name instead. I've tried to use query below:

SELECT TOP 10 b.Amount, a.CustomerName  
FROM       SalesDetail b 
INNER JOIN CustomerProfile a 
        ON a.CustomerNo = b.CustomerNo 
ORDER BY Amount DESC

and it shows repeated customer name, and amount is not summed up. trying to group the customer will prompt an error

SELECT TOP 10 b.Amount, a.CustomerName  
FROM       SalesDetail b 
INNER JOIN CustomerProfile a 
        ON a.CustomerNo = b.CustomerNo 
GROUP BY a.CustomerName 
ORDER BY b.Amount DESC

without inner join, this is where I've made so far. I can sum it up but I'll only get customer No and sales amount.

SELECT  TOP 10 CustomerNo, SUM (Amount) AS 'Total' 
FROM     SalesDetail 
GROUP BY CustomerNo

Thanks for any suggestion.

Upvotes: 0

Views: 1475

Answers (1)

Squirrel
Squirrel

Reputation: 24763

I'm trying to join two tables, one with sales and one with profile. both table has same customer No

Just use INNER JOIN with ON CustomerNo

FROM       SalesDetail s
INNER JOIN CustomerProfile c
        ON s.CustomerNo = c.CustomerNo

so that I can get top 10 highest sum amount sales of customers

You will need to use GROUP BY and ORDER BY together with TOP

SELECT   TOP (10) c.CustomerName, SUM (Amount) AS Total
FROM       SalesDetail s
INNER JOIN CustomerProfile c
        ON s.CustomerNo = c.CustomerNo 
GROUP BY c.CustomerName  
ORDER BY SUM (Amount) DESC

Upvotes: 1

Related Questions