Calc total on joined tables? - 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 2008 Forums

Transact-SQL (2008)

Calc total on joined tables?

Author

Topic

tech
Starting Member

32 Posts

Posted - 2011-03-20 : 16:46:40

OK, I have 4 tables:

Users (has distributorLinkID)

Distributors
Companies
LinkDistCompanies (Joining table for the above 2)

A user joins and has an ID linking to LinkDistCompanies
A User also collects points (a table holds this info per user)

how can I calculate the total where for each distributor and for all companies for a distributor, all the user points are added up for that distributor (for each company)

does this make sense?

So user:

bob is with distributor1, company 2
bill is with distributor2, company 1
jane is with distrubtor1, company 2

I want to show the total for distributor1 and distributor2 by summing up the points/score for that distributor (for each company associated with that distributor)

thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-21 : 05:54:01

please come up with sample data and the required output in light of that data, in order to avoid guessing!

Cheers
MIK

tech
Starting Member

32 Posts

Posted - 2011-03-21 : 13:07:45

its pretty simple. sample data as supplied as output is just a count of numbers.

Users table:

UID
Name
CompanyID (FK NULL)
DistributorLinkID (FK NULL)

UserScores
ID int PK
UserID int FK
Score int

Company Table:

ID int PK
CompanyName

Distributor Table:

DistributorID int PK
DistributorName nvarchar(50)

DistributorCompanies Table:

DCID int PK
CompanyName

DCLink Table:

ID PK int
DistributorID INT FK
DistributorCompanyID INT FK

tech
Starting Member

32 Posts

Posted - 2011-03-21 : 21:02:15

anyone? should be simple but can't think in a simple way!

let me maybe try and make it clearer.

a user can be associated with a company OR distributor and its companies.
a user has a totalpoints table.

If a user is for a distributor, then I want to calculate the total points for each company which belongs to a distributor.

so distributor1 may have 5 companies and 10 users under it
dist2 may have 3 companies and 1 user under it

so a given distributor (passing an ID to it), I want to calculate the SUM of all the user points for each company that is associated with a distributor.

makes sense? :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources