statistics problem - 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 2000 Forums

Transact-SQL (2000)

statistics problem

Author

Topic

jdb1a1
Starting Member

3 Posts

Posted - 2008-07-14 : 22:02:55

I have a unique problem that I have never seen a solution for, nor have I found a solution for it. Here is my problem: I have a table with about 1.5 million records in it. I have a column that is numeric in type, and I want to calculate the standard deviations and averages based on criteria in other fields for this dataset. I would like to use those numbers in my datatable, so I would like to store them in the table, with each row. So for example, say I have a category for dollars spent in a month by a customer (dollarsSpent). I would like to figure out the average in dollarsSpent across group 1, say, cityOfResidence, and also against another group, say, stateOfResidence. I would then like to take each of those values and populate a new column called AVG_by_City, and give each of those records the calculated average.

All I have found in SQL 2000 is the aggregate functions in the T-SQL. The aggregate functions only let you return to queries, which can populate columns, but you cannot use the 'GROUP BY' clause.

I have been beating my head in for about a month trying to figure this one out. Does anybody have any ideas? I would greatly appreciate any help anybody can give me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 23:41:29

quote:
All I have found in SQL 2000 is the aggregate functions in the T-SQL. The aggregate functions only let you return to queries, which can populate columns, but you cannot use the 'GROUP BY' clause

What do you mean by this ? GROUP BY goes together with aggregate functions.

maybe it will be clearer to all if you can post your table structure, some sample data and your required result.

KH
[spoiler]Time is always against us[/spoiler]

jdb1a1
Starting Member

3 Posts

Posted - 2008-07-15 : 07:55:08

It does go with AGGREGATE functions, but SQL 2000 will not let you insert AGGREGATE functions into columns with the GROUP BY statement attached. So, for example, I have a table with four columns. These columns are:

PrimaryKey Salary City Country
1 1000 New York USA
2 2000 London UK
3 6000 Chicago USA
4 4000 New York USA

I want to add an additional column to this table called AverageByCity, and I want it to be populated with the averages sorted by the city. So I want it to look like this:

PrimaryKey Salary City Country AverageByCity
1 1000 New York USA 4000
2 2000 London UK 2000
3 6000 Chicago USA 6000
4 7000 New York USA 4000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-15 : 08:02:57

[code]
select t.PrimaryKey, t.Salary, t.City, t.Country, a.AverageByCity
from table t
inner join
(
select City, AverageByCity = avg(Salary)
from table
group by City
) a on t.City = a.City
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 08:04:47

[code]DECLARE @Sample TABLE
(
PrimaryKey INT IDENTITY(1, 1),
Salary MONEY,
City VARCHAR(20),
Country VARCHAR(20),
AverageByCity MONEY
)

INSERT @Sample
(
Salary,
City,
Country
)
SELECT 1000, 'New York', 'USA' UNION ALL
SELECT 2000, 'London', 'UK' UNION ALL
SELECT 6000, 'Chicago', 'USA' UNION ALL
SELECT 7000, 'New York', 'USA'

SELECT *
FROM @Sample

UPDATE s
SET s.AverageByCity = p.AverageByCity
FROM @Sample AS s
INNER JOIN (
SELECT City,
Country,
AVG(Salary) AS AverageByCity
FROM @Sample
GROUP BY City,
Country
) AS p ON p.City = s.City AND p.Country = s.Country

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

jdb1a1
Starting Member

3 Posts

Posted - 2008-07-15 : 08:58:51

Thanks for the info. I will try the inner join first; it looks like it might be the easiest.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 09:16:08

Both are INNER JOIN!

I have included a complete test case for you. Otherwise we both use INNER JOIN.
The only difference is that khtans query will fail if there is same city in two or more different countries.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources