Best salesperson of each shop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Best salesperson of each shop

Author

Topic

Royal
Starting Member

32 Posts

Posted - 2006-11-14 : 15:01:36

Hi!
How do I return best salesperson of every shop? Not working.. return all the best not one per shop. Thank you so much in advance!

SELECT SUM(Cd.Price) AS Sales, Shop.Name, Personnel.Name
FROM Purchase INNER JOIN
Personnel ON Purchase.Salesperson_id = Personnel.Personnel_id RIGHT OUTER JOIN
Shop ON Personnel.Work_id = Shop.Shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Butik.Namn, Personnel.Name
ORDER BY Sales DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 15:06:07

Some DDL and referential information would be great!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 15:08:32

The trick is to make one derived table with top 1 sum of sales per shop.
Make another table with sum of sales per person.
Join these two queries over SUM.

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-15 : 05:39:11

This is how you start out giving sample data so people here can easely start helping you:

DECLARE @Shop TABLE (Shop_id int, Name varchar(30))
DECLARE @Personnel TABLE (Personnel_id int, Name varchar(30), Work_id int)
DECLARE @Purchase TABLE (Salesperson_id int, Cd_id int)
DECLARE @Cd TABLE (Cd_id int, Price decimal(8,2))

INSERT @Shop (Shop_id, Name)
SELECT 1, 'Little shop of CDs' UNION ALL
SELECT 2, 'My big fat Greek CD shop'  UNION ALL
SELECT 3, 'The no go CD shop'

INSERT @Personnel (Personnel_id, Name, Work_id)
SELECT 1, 'Van the Man', 1 UNION ALL
SELECT 2, 'Peter', 1 UNION ALL
SELECT 3, 'Paul', 2 UNION ALL
SELECT 4, 'Mary', 2

INSERT @Purchase (Salesperson_id, Cd_id)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 3, 1 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 4, 1

INSERT @Cd (Cd_id, Price)
SELECT 1, 4.99 UNION ALL
SELECT 2, 9.99

and then Peso (and maybe some others) will quickly come up with something like:

SELECT S.Name, DP.Name, DS.MaxTotalSale
FROM @Shop S
LEFT OUTER JOIN
(
	SELECT iD.Work_id, MAX(TotalSale) MaxTotalSale
	FROM (
		SELECT P.Work_id, SUM(C.Price) AS TotalSale
		FROM @Personnel P
		INNER JOIN @Purchase B
		ON B.Salesperson_id = P.Personnel_id 
		INNER JOIN @Cd C
		ON C.Cd_id = B.Cd_id
		GROUP BY P.Work_id, P.Personnel_id
	) iD
	GROUP BY iD.Work_id
) DS
ON DS.Work_id = S.Shop_id
LEFT OUTER JOIN (
	SELECT P.Personnel_id, P.Name, SUM(C.Price) TotalSale
    FROM @Personnel P
	INNER JOIN @Purchase B
	ON B.Salesperson_id = P.Personnel_id 
	INNER JOIN @Cd C
	ON C.Cd_id = B.Cd_id
	GROUP BY P.Personnel_id, P.Name
) DP
ON DP.TotalSale = DS.MaxTotalSale

Result

Name                           Name                           MaxTotalSale
------------------------------ ------------------------------ ---------------------------------------
Little shop of CDs             Peter                          19.98
My big fat Greek CD shop       Paul                           9.98
My big fat Greek CD shop       Mary                           9.98
The no go CD shop              NULL                           NULL

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

mr_mist
Grunnio

1870 Posts

Posted - 2006-11-15 : 05:52:29

Can you not just group by sales and shop name?

-------
Moo. :)

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-11-15 : 06:13:21

No, not if you want to get rid of the inferior

SELECT S.Name, D.Name, D.TotalSale
FROM @Shop S
LEFT OUTER JOIN (
	SELECT P.Work_id, P.Name, SUM(C.Price) AS TotalSale
	FROM @Personnel P
	INNER JOIN @Purchase B
	ON B.Salesperson_id = P.Personnel_id 
	INNER JOIN @Cd C
	ON C.Cd_id = B.Cd_id
	GROUP BY P.Work_id, P.Name
) D
ON D.Work_id = S.Shop_id

Name                           Name                           TotalSale
------------------------------ ------------------------------ ---------------------------------------
Little shop of CDs             Peter                          19.98
Little shop of CDs             Van the Man                    14.98
My big fat Greek CD shop       Mary                           9.98
My big fat Greek CD shop       Paul                           9.98
The no go CD shop              NULL                           NULL

Royal
Starting Member

32 Posts

Posted - 2006-11-15 : 09:18:30

Thank you, so helpful! Acctually I need to use it in Reporting services... how should I express "D.Name, D.TotalSale"

SELECT S.Name, D.Name, D.TotalSale
FROM @Shop S
LEFT OUTER JOIN (
SELECT P.Work_id, P.Name, SUM(C.Price) AS TotalSale
FROM @Personnel P
INNER JOIN @Purchase B
ON B.Salesperson_id = P.Personnel_id
INNER JOIN @Cd C
ON C.Cd_id = B.Cd_id
GROUP BY P.Work_id, P.Name
) D
ON D.Work_id = S.Shop_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources