Select all columns when using INNER JOIN

Question

I have SQL code like this :

SELECT
    TB_DataProperti.*,
    TBL_Rating.ISNULL(AVG(Rating), 0) AverageRating
FROM 
    TB_DataProperti
INNER JOIN 
    TBL_Rating ON TB_DataProperti.Kode_Properti = TBL_Rating.Kode_Properti
GROUP BY 
    TB_DataProperti.JudulListing
ORDER BY 
    AverageRating DESC

I get this error:

Msg 8120, Level 16, State 1, Line 3
Column 'TB_DataProperti.Kode_Properti' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I just want to select all data columns using *, because I have many columns

1 `*` is evil. Don't use it. 2. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Aug 24 '17 at 05:43
This error isn't caused by the inner join, it is caused by the group by. When using group by you can only select the columns in the group by or aggregate functions (min/max/avg/..) — Luc, Aug 24 '17 at 05:47

score 0 · Accepted Answer · answered Aug 24 '17 at 06:37

Problem is You are trying to use aggregate function of one table and group by on another table.The rule is if you are using aggregate function with another column then that column should use in group by.Still try this I hope this is useful.

  SELECT
        TB_DataProperti.*,
        ISNULL(AVG(TBL_Rating.Rating), 0)   over (partition by TBL_Rating.Kode_Properti) as AverageRating
    FROM 
        TB_DataProperti
    INNER JOIN 
        TBL_Rating ON TB_DataProperti.Kode_Properti = TBL_Rating.Kode_Properti
    ORDER BY 
        AverageRating DESC

thanks brother.. is worked for me.. – yogie bca Aug 24 '17 at 07:26 — yogie bca, Aug 24 '17 at 07:26

Select all columns when using INNER JOIN

1 Answers1