luffe luffe - 7 months ago 16
SQL Question

Return distinct rows after averaging on group

I have a table

MyTable
that looks something like

periodEndDate TotTermLoans companyName
2009-09-30 NA ABB
2009-09-30 5.38 ABB
2009-09-30 4.34 ABB
2009-12-31 5.6 ABB
2009-12-31 5.6 ABB
2009-12-31 5.6 ABB
2010-03-31 5.47 ABB
2010-03-31 5.47 ABB
2010-03-31 5.0 ABB


I wish to group by year and get the average of
TotTermLoans
for each year, but with my query I get repeated rows:

SELECT YEAR(periodEndDate), AVG(TotTermLoans), companyName
FROM MyTable
GROUP BY YEAR(periodEndDate)


which gives

YEAR(periodEndDate) AVG(TotTermLoans) companyName
2009 5.304 ABB
2009 5.304 ABB
2009 5.304 ABB
2010 5.313 ABB
2010 5.313 ABB
2010 5.313 ABB


I would like

YEAR(periodEndDate) AVG(TotTermLoans) companyName
2009 5.304 ABB
2010 5.313 ABB


Do I have to use sub queries for this? Thank you

Answer
SELECT            YEAR(periodEndDate), AVG(TotTermLoans), companyName 
FROM              MyTable
GROUP BY          YEAR(periodEndDate), companyName

The DISTINCT keyword can also be used on SELECT statements to remove duplicated results.

Comments