Avg count issues - 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)

Avg count issues

Author

Topic

SQLychallenged
Starting Member

2 Posts

Posted - 2013-01-16 : 11:04:06

The query below is succesfully pulling and aggregating data from 2 tables within the same database:

select count(d.pod_number) AS no_count, year(h.adddate) AS Yr, month(h.adddate) AS Mth
from cad_pord_hdrs h, cad_pord_dtls d
where h.cmp_code = d.cmp_code
and h.doc_code = d.doc_code
and h.por_number = d.por_number
group by year(h.adddate), month(h.adddate)

my problems start when I try to apply an average to the query. After numerous google research, it seemed there most obvious solution was to make the select count a subquery of the select avg statement as follows:

SELECT avg(no_count)
FROM (select count(d.pod_number) AS no_count, year(h.adddate) AS Yr, month(h.adddate) AS Mth
from cad_pord_hdrs h, cad_pord_dtls d
where h.cmp_code = d.cmp_code
and h.doc_code = d.doc_code
and h.por_number = d.por_number
group by year(h.adddate), month(h.adddate))

This resulted in the error "Line 7: Incorrect syntax near ')" so I moved the closing bracket to the end of the select count subquery as follows:

SELECT avg(no_count)
FROM (select count(d.pod_number) AS no_count, year(h.adddate) AS Yr, month(h.adddate) AS Mth
from cad_pord_hdrs h, cad_pord_dtls d
where h.cmp_code = d.cmp_code
and h.doc_code = d.doc_code
and h.por_number = d.por_number)
group by year(h.adddate), month(h.adddate)

This resulted in an incorrect syntax near 'group' error. I removed the closing bracket altogether and got : Line 7: Incorrect syntax near ')'. I'm flummoxed...could anyone help me over the finish line by fixing this and explaining my mistake?

Many thanks in advance,

(ps. I'm self taught in case you hadn't already guessed)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-16 : 11:07:39

SQL's parser isn't terribly friendly. You need to alias the subquery
SELECT avg(no_count)
FROM
(select count(d.pod_number) AS no_count, year(h.adddate) AS Yr, month(h.adddate) AS Mth
from cad_pord_hdrs h, cad_pord_dtls d
where h.cmp_code = d.cmp_code
and h.doc_code = d.doc_code
and h.por_number = d.por_number
group by year(h.adddate), month(h.adddate)
) as t1 -- or whatever you want to call it

Jim

Everyday I learn something that somebody else already knew

SQLychallenged
Starting Member

2 Posts

Posted - 2013-01-16 : 11:59:15

Jim - you're a wonder and a marvel! Thankyou so much - it's returning the values :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-17 : 00:01:06

make sure you do this tweak if you need decimal result


SELECT avg(no_count*1.0)
FROM 
(select count(d.pod_number) AS no_count, year(h.adddate) AS Yr, month(h.adddate) AS Mth
from cad_pord_hdrs h, cad_pord_dtls d
where h.cmp_code = d.cmp_code
and h.doc_code = d.doc_code
and h.por_number = d.por_number
group by year(h.adddate), month(h.adddate)
) as t1 -- or whatever you want to call it

see reason here
http://beyondrelational.com/modules/2/blogs/70/posts/10825/beware-of-implicit-conversions.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources