Query of calculations - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query of calculations

Author

Topic

tech
Starting Member

32 Posts

Posted - 2011-03-25 : 14:40:11

I have the following tables:

Users
ID int (PK)
CompanyID int {FK, nullable)
DistributorID int (FK, nullable)

Scores
ID int (PK)
UserID int {FK)
Score int

Company
CompanyID int (PK)
..
..

Distributors
DistributorID int (PK)
DistributorName nvarchar(50)

DistributorCompaniesLink
ID int (PK)
CompanyID int (FK)
DistributorID int (FK)

A User is associated with a company OR distributor
A User has scores.
A Distributor has one or more companies associated to it.

I want to be able to calculate the sum of all scores for users for all companies which belong to a distributor.

makes sense? not sure how to think of the query! I also want to do this across all distributors.

so if Distributor1 has 3 companies and there are a mixture of number of users per company, I want to calculate the total scores for all companies for the distributor.

srujanavinnakota
Starting Member

34 Posts

Posted - 2011-03-25 : 16:45:48

try this..

Select
Distributors.DistributorName,
Company.CompanyName,
Sum(Score) as Score
from
DistributorCompaniesLink
INNER JOIN Distributors ON DistributorCompaniesLink.DistributorID=Distributors.DistributorID
INNER JOIN Company ON DistributorCompaniesLink.CompanyID=Company.CompanyID
/**If User is assosiated with company use company table or if not use distibuter table**/
INNER JOIN Users ON Company.UsersID=Users.UsersId
INNER JOIN Scores ON Scores.UsersID=Users.UsersId
GROUP BY
Distributors.DistributorName,
Company.CompanyName

you could do this in sub query format also.

tech
Starting Member

32 Posts

Posted - 2011-03-29 : 15:58:01

Thanks. Problem I have is that when I implement paging on the SPROC, I get an error when executing saying that there was more than 1 subquery results that were returned....
different query but you get the hint

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE dbo.[GetLeaderboardCompanyScores] (
@itemsPerPage int,
@pageNumber int,
@totalRecords int OUTPUT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @firstRow int
DECLARE @lastRow int

SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords = (SELECT COUNT(c.[CompanyID])
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
GROUP BY c.CompanyID);

WITH ManyItems AS (
SELECT c.CompanyID, c.CompanyName, ISNULL(SUM(ua.TotalPoints), 0) AS CompanyPoints, ROW_NUMBER() OVER (ORDER BY ua.TotalPoints DESC) AS RowNumber
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
GROUP BY c.CompanyID, c.CompanyName, ua.TotalPoints
)
SELECT RowNumber, CompanyID, CompanyName, CompanyPoints
FROM ManyItems
WHERE RowNumber BETWEEN @firstRow AND @lastRow
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources