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.
| 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/
|
|
|
|
|
|
|
Avg count issues