issue with averages - 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)

issue with averages

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-08-16 : 02:47:25

Hi All,

I have a small requiremnt in calculating averages.

CREATE TABLE ExcelData
(yr int,
CollegeName varchar(20),
race varchar(20),
gender varchar(20),
history varchar(10),
science varchar(10),
commerce varchar(10)
)
insert into ExcelData
select 2003,'0156211','B','M',230,390,NULL
union all
select 2003,'0156211','B','M',230,390,NULL
union all
select 2003,'0176111','B','M',710,640,NULL
union all
select 2003,'0176111','B','M',710,640,NULL
union all
select 2003,'2222222','B','M',500,500,NULL

Means 5 Students have taken history,science,commerce assements.

select * from ExcelData

yr CollegeName race gender history science commerce
2003 0156211 B M 230 390 NULL
2003 0156211 B M 230 390 NULL -- duplicate rec

2003 0176111 B M 710 640 NULL
2003 0176111 B M 710 640 NULL -- duplicate rec

2003 2222222 B M 500 500 NULL

select COUNT(*) from ExcelData
--5

-- Calculate avg in ExcelData
select AVG(convert(decimal(9,2),history)) as history_avg,
AVG(convert(decimal(9,2),science)) as science_avg,
AVG(convert(decimal(9,2),commerce)) as commerce_avg
from ExcelData

history_avg science_avg commerce_avg
476.000000 512.000000 NULL

drop table [staging_tbl]

CREATE TABLE [dbo].[staging_tbl](
[currentSchoolYear] [varchar](10) NULL,
[Dist] [varchar](10) NULL,
school [varchar](10) NULL,
block_id [varchar](10) NULL,
race varchar(10),
gender varchar(10),
[Subject] [varchar](10) NULL,
[STUDENT_COUNT] [varchar](10) NULL,
marks [varchar](10) NULL
)

SELECT * FROM staging_tbl

-- INSERT DATA
INSERT INTO staging_tbl
select yr as currentSchoolYear,
substring(CollegeName,1,3) as Dist,
substring(CollegeName,4,2) as SCHOOL_ID,
substring(CollegeName,6,2) as block_id,
race,
gender,
[subject],
0 as STUDENT_COUNT, /* initially zero */
marks as marks
from (
select CollegeName,yr,race,gender,case when history IS NULL then '0'

else history end as history,case when science IS null then '0' else

science end as science,case when [commerce] IS null then '0' else

commerce end as commerce from ExcelData) p
unpivot
(marks for [subject] in (history,science,commerce)
) AS Unpvt

SELECT * FROM staging_tbl

-- Extract data

-- HISTORY Average marks
SELECT * FROM staging_tbl WHERE Subject = 'HISTORY'

2003 015 62 11 B M history 0 230
2003 015 62 11 B M history 0 230

2003 017 61 11 B M history 0 710
2003 017 61 11 B M history 0 710

2003 222 22 22 B M history 0 500

----

476.000000

-----

SELECT AVG(CONVERT(decimal(9,2),marks)) FROM staging_tbl where

[subject] = 'history'
476.000000

-- Science Average marks
SELECT * FROM staging_tbl WHERE Subject = 'SCIENCE'
2003 015 62 11 B M science 0 390
2003 015 62 11 B M science 0 390

2003 017 61 11 B M science 0 640
2003 017 61 11 B M science 0 640

2003 222 22 22 B M science 0 500

SELECT AVG(CONVERT(decimal(9,2),marks)) FROM staging_tbl where

[subject] = 'SCIENCE'

512.000000 -- compare with excel Yes! Match

-- COMMERCE marks
select * from staging_tbl where Subject='COMMERCE'
2003 015 62 11 B M commerce 0

0
2003 015 62 11 B M commerce 0

0
2003 017 61 11 B M commerce 0

0
2003 017 61 11 B M commerce 0

0
2003 222 22 22 B M commerce 0

0

SELECT AVG(CONVERT(decimal(9,2),marks)) FROM staging_tbl where

[subject] = 'COMMERCE'
0.000000

SELECT * FROM staging_tbl

select currentSchoolYear,
Dist,
school,
race,
gender,
[Subject],
COUNT(*) as student_cnt,
AVG(convert(decimal(9,2),marks)) as avg_score into TargetTbl

/*fct tbl*/
from staging_tbl
group by currentSchoolYear,
Dist,
school,
race,
gender,
[Subject]

select * from TargetTbl order by [subject]

currentSchoolYear Dist school race gender Subject

student_cnt avg_score
2003 015 62 B M commerce 2

0.000000
2003 017 61 B M commerce 2

0.000000
2003 222 22 B M commerce 1

0.000000
2003 222 22 B M history 1 500.000000
2003 017 61 B M history 2 710.000000
2003 015 62 B M history 2 230.000000
2003 015 62 B M science 2 390.000000
2003 017 61 B M science 2 640.000000
2003 222 22 B M science 1 500.000000

--Now again Calculate Average subject wise and i need to get back the

original values which i have caluclated in "Excel" and "Staging table"

select AVG(convert(decimal(9,2),avg_score)) from TargetTbl
where [subject] = 'history'

480.000000 Actaul Value: 476.000000

select AVG(convert(decimal(9,2),avg_score)) from TargetTbl
where [subject]= 'science'

510.000000 Actaul Value: 512.000000

select AVG(convert(decimal(9,2),avg_score)) from TargetTbl
where [subject]= 'commerce'

0.000000 Actaul Value: 0.00000

By the above 3 results it is clear that due to duplicate records there

is a mismatch
in Actual average.

Is there any solution or work around so that i can get back my Averages

as Actual ones.
Can we introduce any key or logically rownumber to fix this??????

Without changing the structure is there are good way to fix this???

Current the Key for the Target table is

(currentSchoolYear,Dist,school,race,gender,subject).

Any help would be greatly appreciated!!!

Thanks in Advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-16 : 04:49:48

delete the duplicate record ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources