What am I missing? - 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

General SQL Server Forums

New to SQL Server Programming

What am I missing?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-19 : 10:50:12

I'm getting this error message on this query:

Column 'd.doc' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I do have a GROUP BY clause listed:


insert into PendingDiary 
Select  d.doc, isnull(totos,0) as totovrs, isnull(totps,0) as totpnds, isnull(toto,0) as totovr, SUM(cast(totp as int)) as totpnd, d.weekdat, d.dowrdat   
from
(select doc, totos = '0', count(doc) as toto
from #temppend
where clear = 'O' and SDW = 'N' 
group by doc
)c
right join
(select doc, totps = '0', count(doc) as totp, weekdat, dowrdat 
from #temppend
where SDW = 'N'
group by doc, weekdat, dowrdat 
)d
on d.doc = c.doc
order by doc

What am I missing?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 11:52:21

Try this

Select  d.doc
, isnull(c.totos,0) as totovrs
, d.totps as totpnds
, isnull(c.toto,0) as totovr
, SUM(d.totp) as totpnd
, d.weekdat
, d.dowrdat   
from
(select doc, totps = '0', count(doc) as totp, weekdat, dowrdat 
from #temppend
where SDW = 'N'
group by doc, weekdat, dowrdat 
)d
left join
(select doc, totos = '0', count(doc) as toto
from #temppend
where clear = 'O' and SDW = 'N' 
group by doc
)c
on d.doc = c.doc
group by d.doc,c.totos,d.totps,c.toto,d.weekdat,d.dowrdat

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-05-19 : 15:32:01

THANKS THAT WORKED!!!!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-19 : 15:39:06

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources