SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Avg count issues		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLychallenged
Starting Member

United Kingdom
2 Posts
Posted - 01/16/2013 :  11:04:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 01/16/2013 :  11:07:39  Show Profile  Reply with Quote
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
Go to Top of Page

SQLychallenged
Starting Member

United Kingdom
2 Posts
Posted - 01/16/2013 :  11:59:15  Show Profile  Reply with Quote
Jim - you're a wonder and a marvel! Thankyou so much - it's returning the values :-)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47157 Posts
Posted - 01/17/2013 :  00:01:06  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000