By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,543 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,543 IT Pros & Developers. It's quick & easy.

counting field totals as a formula

P: n/a
Hi,

I'm trying to count field login totals for users which updates another field
in another table. Which is the most efficient method?

I don't want to use a standard query as it will take too long if there are
1000 users per company each with 1000 plus logins.

I was thinking in terms of either a function, or a formula (using the built-
in formula field within mssql).

the query though (as its the only way which i'm familiar) is:

SELECT SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
WHERE (CompanyName = x) AND (EmployerID = y)

how would i write this as a formula or as a function?
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
James M via SQLMonster.com (fo***@SQLMonster.com) writes:
I'm trying to count field login totals for users which updates another
field in another table. Which is the most efficient method?

I don't want to use a standard query as it will take too long if there are
1000 users per company each with 1000 plus logins.
Depends on what you mean with a "standard query", but if you mean one
that is ANSI-compliant you are likely to be right.
I was thinking in terms of either a function, or a formula (using the
built- in formula field within mssql).


Ehum, there is no "build-in formula field" in SQL Server. There are
computed columns, which may appear as "formula fields" in Enterprise
Manager.

Anyway, I don't think a computed column would be a good idea, since that
would have to be a scalar UDF, and they are not known for being
performance boosters. You could index the column, but there is some
hassle with this.

Anyway, this query should take you a long way:

UPDATE tbl
SET logintotals = e.TotalLogons
FROM tbl t
JOIN (SELECT CompanyName, EmployerID,
SUM(NumberOfLogons) AS TotalLogons
FROM EmployerProfileDB39
GROUP BY CompanyName, EmployerID) AS e
ON t.CompanyName = e.CompanyName
AND t.EmployeeID = e.EmployeeID


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.