Error Group By Clause - 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

Error Group By Clause

Author

Topic

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-28 : 12:39:58

err#-2147217900
err desc:Column 'tblYCampaigns.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

sql:SELECT tblYCampaigns.ID, tblYCampaigns.Status,tblYCampaigns.StatusReason, tblYCampaigns.Name, tblYCampaignsStats.DateTime,Sum(tblYCampaignsStats.Impressions),AVG(tblYCampaignsStats.CTR),SUM(tblYCampaignsStats.Clicks),AVG(tblYCampaignsStats.CPC),SUM(tblYCampaignsStats.Assists),SUM(tblYCampaignsStats.Conversions),SUM(tblYCampaignsStats.Cost),SUM(tblYCampaignsStats.MarketHealthSales),SUM(tblYCampaignsStats.MarketHealthSalesValue) FROM tblYCampaigns, tblYCampaignsStats WHERE tblYCampaignsStats.YCampaigns_ID = tblYCampaigns.ID AND tblYCampaignsStats.DateTime >= '7/24/2010' AND tblYCampaignsStats.DateTime <= '7/28/2010' AND tblYCampaigns.Company_ID=1 ORDER BY Impressions DESC

Why would i have to use a Group BY Clause for this statement? Is there any way I could avoid this?

Thanks

Kristen
Test

22859 Posts

Posted - 2010-07-28 : 12:53:38

"Why would i have to use a Group BY Clause for this statement?"

Sum(tblYCampaignsStats.Impressions) and all the other AVG and SUM statements

"Is there any way I could avoid this?"

Is it a problem to use GROUP BY ?

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-28 : 13:02:13

Hi Kristen,

If I use a Group By Clause I would have to get rid of the "Status,StatusReason,Name making me run another query. I was hopping to keep it all in one query if possible.

Thanks for the help.

Cyberskull
Starting Member

6 Posts

Posted - 2010-07-28 : 13:09:36

I just ended up doing this:

SQL = "SELECT tblYCampaigns.ID," &_
" Sum(tblYCampaignsStats.Impressions) AS Impressions,AVG(tblYCampaignsStats.CTR) AS CTR,SUM(tblYCampaignsStats.Clicks) AS Clicks,AVG(tblYCampaignsStats.CPC) AS CPC," &_
" SUM(tblYCampaignsStats.Assists) AS Assists,SUM(tblYCampaignsStats.Conversions) AS Conversions,SUM(tblYCampaignsStats.Cost) AS Cost," &_
" SUM(tblYCampaignsStats.MarketHealthSales) AS MarketHealthSales,SUM(tblYCampaignsStats.MarketHealthSalesValue) AS MarketHealthSalesValue" &_
" FROM tblYCampaigns, tblYCampaignsStats" &_
" WHERE tblYCampaignsStats.YCampaigns_ID = tblYCampaigns.ID" &_
" AND tblYCampaignsStats.DateTime >= '" & Session("DateFrom") & "'" &_
" AND tblYCampaignsStats.DateTime <= '" & Session("DateTo") & "'" &_
" AND tblYCampaigns.Company_ID=" & Session("CompanyID") &_
" GROUP BY tblYCampaigns.ID "

I'll grab the name of the campaign through a different function

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources