databasename.d.first_column_in_the_table' isn't in GROUP BY

Question

i always get the same error whenever i run this query. i tried using simple query to test if there is something wrong in the query. I also noticed that error happened when im using the view in my query.

CREATE OR REPLACE VIEW v_vss_car_wash AS

     SELECT
     max(r.id) AS id
    ,d.id AS dealer_id
    ,d.dealer_code
    ,d.dealer_name
    ,count(*) AS total_respondents
    ,sum(car_washed) AS car_washed                  -- Car Washed
    ,count(*) - sum(car_washed) AS car_unwashed     -- Car Unwashed
    ,sum(IF (car_washed AND car_satisfied, 1, 0)) AS car_satisfied
    ,sum(IF (car_washed AND NOT car_satisfied, 1, 0)) AS car_unsatisfied
    ,MONTH(r.create_date) AS create_month
    ,YEAR(r.create_date) AS create_year
FROM t_vss_survey_response r 
    LEFT JOIN t_vss_dealer d ON (r.dealer_id = d.id)
WHERE survey_code = "ASS"
GROUP BY dealer_code, YEAR(r.create_date), MONTH(r.create_date);

then this is my query im using(very simple) but i always get the same error.

select a.dealer_name, a.dealer_code from v_vss_car_wash a

now, this is the error message

Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1:'crmsdbdev.d.dealer_name' isn't in GROUP BY ; com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 'crmsdbdev.d.dealer_name' isn't in GROUP BY

Answer 1

Just add the appropriate columns to the group by:

CREATE OR REPLACE VIEW v_vss_car_wash AS
SELECT
     max(r.id) AS id
    ,d.id AS dealer_id
    ,d.dealer_code
    ,d.dealer_name
    ,count(*) AS total_respondents
    ,sum(car_washed) AS car_washed                  -- Car Washed
    ,count(*) - sum(car_washed) AS car_unwashed     -- Car Unwashed
    ,sum(IF (car_washed AND car_satisfied, 1, 0)) AS car_satisfied
    ,sum(IF (car_washed AND NOT car_satisfied, 1, 0)) AS car_unsatisfied
    ,MONTH(r.create_date) AS create_month
    ,YEAR(r.create_date) AS create_year
FROM t_vss_survey_response r 
    LEFT JOIN t_vss_dealer d ON (r.dealer_id = d.id)
WHERE survey_code = "ASS"
GROUP BY d.id, d.dealer_code, d.dealer_name,
         YEAR(r.create_date), MONTH(r.create_date);