Question on 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

SQL Server 2005 Forums

Transact-SQL (2005)

Question on Group by Clause

Author

Topic

csecharith
Starting Member

21 Posts

Posted - 2008-01-15 : 23:55:08

Hi,

I want to know a solution for this group by issue. I want to include 'LocalCurrencyID' to the select list and I don't want to include it in the Group by clause. Then I get the following error;

"Column 'dbo.Security.LocalCurrencyID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Do you know any solution for this issue? And what is the concept behind this constraint.

Thanks!
Charith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 00:01:26

You need to move part containing group by to subquery and take inner join of main query with this on grouping values.If you want full query, please post your current query.
The reason for error is once you group by a value you can use only fields with aggregation applied on them execpt for field which is used for grouping. So you either need to add your need field on group by or move group by to subqueries.

csecharith
Starting Member

21 Posts

Posted - 2008-01-16 : 00:41:02

Hi,

Thank you so much, here is the full query.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT TOP (100) PERCENT dbo.Company.Name AS CompanyName, dbo.Category.Description AS Category, dbo.Sector.Description AS SubCategory,
ROUND(dbo.GetCurrentRate(dbo.Security.LocalCurrencyID) * SUM(dbo.Security.ValuationYearToDateInLocalCcy), 0) AS ValuationYearToDateInUSD,
dbo.GetPercentageOfTotalPortfolioForCategoryAndSubCategoryByYTD(dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.CompanyID)
AS PortfolioPercentageCurrentYearByYTD, ROUND(SUM(dbo.Security.YearEndValuation * dbo.GetRateAtQuarterEnd(4, YEAR(GETDATE()) - 1,
dbo.Security.LocalCurrencyID)), 0) AS YearEndValuation,
dbo.GetPercentageOfTotalPortfolioForCategoryAndSubCategoryByYEV(dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.CompanyID)
AS PortfolioPercentagePriorYrEndValuation, dbo.GetAdditionsRedemptionsForCategoryAndSubCategoryInUSD(dbo.Security.CompanyID,
dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.LocalCurrencyID, YEAR(GETDATE()), MONTH(GETDATE())) AS YTDMovement,
dbo.GetPerformanceBycategory(ROUND(dbo.GetCurrentRate(dbo.Security.LocalCurrencyID) * SUM(dbo.Security.ValuationYearToDateInLocalCcy), 0),
dbo.GetAdditionsRedemptionsForCategoryAndSubCategoryInUSD(dbo.Security.CompanyID, dbo.Security.CategoryID, dbo.Security.SectorID,
dbo.Security.LocalCurrencyID, YEAR(GETDATE()), MONTH(GETDATE())), ROUND(SUM(dbo.Security.YearEndValuation * dbo.GetRateAtQuarterEnd(4,
YEAR(GETDATE()) - 1, dbo.Security.LocalCurrencyID)), 0)) AS PerformanceByCategory, dbo.Security.CompanyID
FROM dbo.Security INNER JOIN
dbo.Category ON dbo.Security.CategoryID = dbo.Category.ID INNER JOIN
dbo.Company ON dbo.Security.CompanyID = dbo.Company.ID INNER JOIN
dbo.Currency ON dbo.Security.LocalCurrencyID = dbo.Currency.ID INNER JOIN
dbo.FundType ON dbo.Security.FundTypeID = dbo.FundType.ID INNER JOIN
dbo.Sector ON dbo.Security.SectorID = dbo.Sector.ID
WHERE (dbo.FundType.Name = dbo.GetProductCodeByFundType('HedgeFund')) AND (dbo.Security.NominalHolding > 0)
GROUP BY dbo.Company.Name, dbo.Category.Description, dbo.Sector.Description, dbo.Security.LocalCurrencyID, dbo.Security.CategoryID,
dbo.Security.SectorID, dbo.Security.CompanyID
ORDER BY CompanyName

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I want to remove following columns from group by clause "dbo.Security.LocalCurrencyID, dbo.Security.CategoryID,
dbo.Security.SectorID, dbo.Security.CompanyID"

Thanks a lot.

Charith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 01:05:23

quote:
Originally posted by csecharith

Hi,

Thank you so much, here is the full query.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT TOP (100) PERCENT dbo.Company.Name AS CompanyName, dbo.Category.Description AS Category, dbo.Sector.Description AS SubCategory,
ROUND(dbo.GetCurrentRate(dbo.Security.LocalCurrencyID) * SUM(dbo.Security.ValuationYearToDateInLocalCcy), 0) AS ValuationYearToDateInUSD,
dbo.GetPercentageOfTotalPortfolioForCategoryAndSubCategoryByYTD(dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.CompanyID)
AS PortfolioPercentageCurrentYearByYTD, ROUND(SUM(dbo.Security.YearEndValuation * dbo.GetRateAtQuarterEnd(4, YEAR(GETDATE()) - 1,
dbo.Security.LocalCurrencyID)), 0) AS YearEndValuation,
dbo.GetPercentageOfTotalPortfolioForCategoryAndSubCategoryByYEV(dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.CompanyID)
AS PortfolioPercentagePriorYrEndValuation, dbo.GetAdditionsRedemptionsForCategoryAndSubCategoryInUSD(dbo.Security.CompanyID,
dbo.Security.CategoryID, dbo.Security.SectorID, dbo.Security.LocalCurrencyID, YEAR(GETDATE()), MONTH(GETDATE())) AS YTDMovement,
dbo.GetPerformanceBycategory(ROUND(dbo.GetCurrentRate(dbo.Security.LocalCurrencyID) * SUM(dbo.Security.ValuationYearToDateInLocalCcy), 0),
dbo.GetAdditionsRedemptionsForCategoryAndSubCategoryInUSD(dbo.Security.CompanyID, dbo.Security.CategoryID, dbo.Security.SectorID,
dbo.Security.LocalCurrencyID, YEAR(GETDATE()), MONTH(GETDATE())), ROUND(SUM(dbo.Security.YearEndValuation * dbo.GetRateAtQuarterEnd(4,
YEAR(GETDATE()) - 1, dbo.Security.LocalCurrencyID)), 0)) AS PerformanceByCategory, dbo.Security.CompanyID
FROM dbo.Security INNER JOIN
dbo.Category ON dbo.Security.CategoryID = dbo.Category.ID INNER JOIN
dbo.Company ON dbo.Security.CompanyID = dbo.Company.ID INNER JOIN
dbo.Currency ON dbo.Security.LocalCurrencyID = dbo.Currency.ID INNER JOIN
dbo.FundType ON dbo.Security.FundTypeID = dbo.FundType.ID INNER JOIN
dbo.Sector ON dbo.Security.SectorID = dbo.Sector.ID
WHERE (dbo.FundType.Name = dbo.GetProductCodeByFundType('HedgeFund')) AND (dbo.Security.NominalHolding > 0)
GROUP BY dbo.Company.Name, dbo.Category.Description, dbo.Sector.Description, dbo.Security.LocalCurrencyID, dbo.Security.CategoryID,
dbo.Security.SectorID, dbo.Security.CompanyID
ORDER BY CompanyName

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

I want to remove following columns from group by clause "dbo.Security.LocalCurrencyID, dbo.Security.CategoryID,
dbo.Security.SectorID, dbo.Security.CompanyID"

Thanks a lot.

Charith

sorry cant make out what you are trying to achieve here. You told you wannted to remove Security fields from group but you are never aggregating on other fields. Can you explain this query a bit so that i get a rough idea before changing? If you can,give some sample data too so that idea gets clearer. Thanks.

csecharith
Starting Member

21 Posts

Posted - 2008-01-16 : 01:59:32

Hi,

This is a very complex database. I just want to know how to do it?

Can you explain what you meant by....
" You need to move part containing group by to subquery and take inner join of main query with this on grouping values."

Can you change my query accordingly?

Thanks!
Charith

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 03:01:25

the problem is here you group by fields from other tables but are not taking any aggregation based on them. Also you want to remove Security fields from group by but you are summing on fields of it. So unless you give me info on what you are trying to achieve out of these tables i wont be able to direct you to a successful solution.

csecharith
Starting Member

21 Posts

Posted - 2008-01-16 : 03:19:48

Hi,

This is very complex and it will take long time to explain. Forget that case.

Lets say you have a table called Student, which contains ID,FirstName,LastName,Age,School etc. I want to write a sql to retrieve all fields and want to group by School.

Eg:
SELECT * FROM Student GROUP BY School

So, this will give the same error. And how you solution work for this case?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 03:32:22

in that case you need to do like this:-
SELECT s.*,t.<aggreqty1>,t.<aggregty2>...
FROM Student s
INNER JOIN (SELECT School,<your aggregated fields based on school>
FROM Student GROUP BY SChool)t
ON t.School=s.School

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources