counting field totals as a formula

[James M via SQLMonster.com]

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?

[Erland Sommarskog]

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