Grouping & Summarizing problems

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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping & Summarizing problems

Author

Topic

ccajina
Starting Member

1 Post

Posted - 2002-12-26 : 19:12:14

Hi. I'm having touble with an SQL query which results should be grouped by two different criteria. I'm using three tables in a Many-To-Many relationship. For simplicity, consider the table structure as follows:

CLIENTS (clientID [PK], clientName)
SALES (clientID, businessTypeID, salesAmount)
BUSINESSES (businessTypeID [PK], businessTypeDescription)

The results should be like this:

For each businessTypeID, businessTypeDescription the query must show information of the best clients, that is, clientID [PK], clientName and the SUM of salesAmount

NOTE: I think the first step would be to perform a SUM function on the SALES table to get client totals, then, filter the best clients BY BUSINESS TYPE using a MAX function on the first result set, and displaying the business type information as well.

Any suggestions????

Thanks in advance!!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-26 : 20:23:21

CREATE TABLE #Sales (clientID int, businessTypeID int, salesAmount money)

INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (1,1,1.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (2,1,2.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (3,1,2.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (1,2,3.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (2,2,2.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (3,2,2.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (1,3,4.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (2,3,2.0)
INSERT INTO #Sales (clientID, businessTypeID, salesAmount) VALUES (3,3,4.0)

CREATE TABLE #Businesses (businessTypeID int, businessTypeDescription nvarchar(1))

INSERT INTO #Businesses (businessTypeID, businessTypeDescription) VALUES (1,'A')
INSERT INTO #Businesses (businessTypeID, businessTypeDescription) VALUES (2,'B')
INSERT INTO #Businesses (businessTypeID, businessTypeDescription) VALUES (3,'C')

CREATE TABLE #Clients (clientID int, clientName nvarchar(1))

INSERT INTO #Clients (clientID, clientName) VALUES (1,'X')
INSERT INTO #Clients (clientID, clientName) VALUES (2,'Y')
INSERT INTO #Clients (clientID, clientName) VALUES (3,'Z')

SELECT A.clientID, D.clientName, B.businessTypeID, C.businessTypeDescription, B.MaxOfSales
FROM
#Sales A INNER JOIN
(
SELECT businessTypeID, Max(salesAmount) MaxOfSales
FROM #Sales
GROUP BY businessTypeID
) B ON A.businessTypeID = B.businessTypeID AND A.salesAmount = B.MaxOfSales
LEFT JOIN #Businesses C ON B.businessTypeID = C.businessTypeID
LEFT JOIN #Clients D ON A.ClientID = D.ClientID
ORDER BY B.BusinessTypeID, A.clientID

DROP TABLE #Sales
DROP TABLE #Businesses
DROP TABLE #Clients

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources