Error in Google Data Studio when using MySQL as data source





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







1















I encounter the following problem with MySQL in combination with data studio and am frankly quite lost: 



SELECT 

user.name,

user.second_name,

school.name,

SUM(booked_lessons.duration),

SUM(total_lessons.duration),

SUM(theory_lessons.duration)

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;`


When I include import the Code as a MySQL Datasource in Google Data Studio everything seems to work out fine at first.
Yet, when I want to analyze the data in charts I do get the following error message: Error 1



Even more confusing is the following:



when I use this code: 



`SELECT 

user.name 'Vorname',

user.second_name 'Nachname',

lesson.instructor_id 'FL ID',

school.name 'Standort',

SUM(offered_lessons.duration) 'noch buchbare Praxisstunden',

SUM(booked_lessons.duration) 'gebuchte Praxisstunden',

SUM(total_week_lessons.duration) 'angebotene Praxis gesamt',

SUM(theory_lessons.duration) 'Theorieunterricht',

SUM(exam_lessons.duration) 'Prüfungen',

SUM(total_lessons.duration) 'angebotene Stunden gesamt',

SUM(activity_lessons.duration) 'Sonstige Tätigkeiten',

SUM(canceled_lessons.duration) 'abgesagt von 123 <48',

SUM(canceled_48_lessons.duration) 'abgesagt von 123 >48',

SUM(canceled_student_lessons.duration) 'abgesagt von Schueler <48',

SUM(canceled_48_student_lessons.duration) 'abgesagt von Schueler >48',

SUM(canceled_instructor_lessons.duration) 'abgesagt von FL <48',

SUM(canceled_48_instructor_lessons.duration) 'abgesagt von FL >48'

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NULL) AS offered_lessons ON offered_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving') AS total_week_lessons ON total_week_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'exam') AS exam_lessons ON exam_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'activity') AS activity_lessons ON activity_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_lessons ON canceled_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_48_lessons ON canceled_48_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_student_lessons ON canceled_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_48_student_lessons ON canceled_48_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND instructor_Id = lesson.cancelled_by_id) AS canceled_instructor_lessons ON canceled_instructor_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND Instructor_Id = lesson.cancelled_by_id) AS canceled_48_instructor_lessons ON canceled_48_instructor_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;


`
The code is almost identical. But now I get this error message:



Error 2



Can anyone help me out here?



Thanks in advance.






mysql mysql-workbench google-data-studio







share|improve this question

























  • have you check google studio data source connecting with mysql

    – Kandy
    Jan 4 at 11:19


















1















I encounter the following problem with MySQL in combination with data studio and am frankly quite lost: 



SELECT 

user.name,

user.second_name,

school.name,

SUM(booked_lessons.duration),

SUM(total_lessons.duration),

SUM(theory_lessons.duration)

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;`


When I include import the Code as a MySQL Datasource in Google Data Studio everything seems to work out fine at first.
Yet, when I want to analyze the data in charts I do get the following error message: Error 1



Even more confusing is the following:



when I use this code: 



`SELECT 

user.name 'Vorname',

user.second_name 'Nachname',

lesson.instructor_id 'FL ID',

school.name 'Standort',

SUM(offered_lessons.duration) 'noch buchbare Praxisstunden',

SUM(booked_lessons.duration) 'gebuchte Praxisstunden',

SUM(total_week_lessons.duration) 'angebotene Praxis gesamt',

SUM(theory_lessons.duration) 'Theorieunterricht',

SUM(exam_lessons.duration) 'Prüfungen',

SUM(total_lessons.duration) 'angebotene Stunden gesamt',

SUM(activity_lessons.duration) 'Sonstige Tätigkeiten',

SUM(canceled_lessons.duration) 'abgesagt von 123 <48',

SUM(canceled_48_lessons.duration) 'abgesagt von 123 >48',

SUM(canceled_student_lessons.duration) 'abgesagt von Schueler <48',

SUM(canceled_48_student_lessons.duration) 'abgesagt von Schueler >48',

SUM(canceled_instructor_lessons.duration) 'abgesagt von FL <48',

SUM(canceled_48_instructor_lessons.duration) 'abgesagt von FL >48'

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NULL) AS offered_lessons ON offered_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving') AS total_week_lessons ON total_week_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'exam') AS exam_lessons ON exam_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'activity') AS activity_lessons ON activity_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_lessons ON canceled_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_48_lessons ON canceled_48_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_student_lessons ON canceled_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_48_student_lessons ON canceled_48_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND instructor_Id = lesson.cancelled_by_id) AS canceled_instructor_lessons ON canceled_instructor_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND Instructor_Id = lesson.cancelled_by_id) AS canceled_48_instructor_lessons ON canceled_48_instructor_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;


`
The code is almost identical. But now I get this error message:



Error 2



Can anyone help me out here?



Thanks in advance.






mysql mysql-workbench google-data-studio







share|improve this question

























  • have you check google studio data source connecting with mysql

    – Kandy
    Jan 4 at 11:19














1












1








1








I encounter the following problem with MySQL in combination with data studio and am frankly quite lost: 



SELECT 

user.name,

user.second_name,

school.name,

SUM(booked_lessons.duration),

SUM(total_lessons.duration),

SUM(theory_lessons.duration)

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;`


When I include import the Code as a MySQL Datasource in Google Data Studio everything seems to work out fine at first.
Yet, when I want to analyze the data in charts I do get the following error message: Error 1



Even more confusing is the following:



when I use this code: 



`SELECT 

user.name 'Vorname',

user.second_name 'Nachname',

lesson.instructor_id 'FL ID',

school.name 'Standort',

SUM(offered_lessons.duration) 'noch buchbare Praxisstunden',

SUM(booked_lessons.duration) 'gebuchte Praxisstunden',

SUM(total_week_lessons.duration) 'angebotene Praxis gesamt',

SUM(theory_lessons.duration) 'Theorieunterricht',

SUM(exam_lessons.duration) 'Prüfungen',

SUM(total_lessons.duration) 'angebotene Stunden gesamt',

SUM(activity_lessons.duration) 'Sonstige Tätigkeiten',

SUM(canceled_lessons.duration) 'abgesagt von 123 <48',

SUM(canceled_48_lessons.duration) 'abgesagt von 123 >48',

SUM(canceled_student_lessons.duration) 'abgesagt von Schueler <48',

SUM(canceled_48_student_lessons.duration) 'abgesagt von Schueler >48',

SUM(canceled_instructor_lessons.duration) 'abgesagt von FL <48',

SUM(canceled_48_instructor_lessons.duration) 'abgesagt von FL >48'

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NULL) AS offered_lessons ON offered_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving') AS total_week_lessons ON total_week_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'exam') AS exam_lessons ON exam_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'activity') AS activity_lessons ON activity_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_lessons ON canceled_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_48_lessons ON canceled_48_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_student_lessons ON canceled_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_48_student_lessons ON canceled_48_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND instructor_Id = lesson.cancelled_by_id) AS canceled_instructor_lessons ON canceled_instructor_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND Instructor_Id = lesson.cancelled_by_id) AS canceled_48_instructor_lessons ON canceled_48_instructor_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;


`
The code is almost identical. But now I get this error message:



Error 2



Can anyone help me out here?



Thanks in advance.






mysql mysql-workbench google-data-studio







share|improve this question
















I encounter the following problem with MySQL in combination with data studio and am frankly quite lost: 



SELECT 

user.name,

user.second_name,

school.name,

SUM(booked_lessons.duration),

SUM(total_lessons.duration),

SUM(theory_lessons.duration)

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;`


When I include import the Code as a MySQL Datasource in Google Data Studio everything seems to work out fine at first.
Yet, when I want to analyze the data in charts I do get the following error message: Error 1



Even more confusing is the following:



when I use this code: 



`SELECT 

user.name 'Vorname',

user.second_name 'Nachname',

lesson.instructor_id 'FL ID',

school.name 'Standort',

SUM(offered_lessons.duration) 'noch buchbare Praxisstunden',

SUM(booked_lessons.duration) 'gebuchte Praxisstunden',

SUM(total_week_lessons.duration) 'angebotene Praxis gesamt',

SUM(theory_lessons.duration) 'Theorieunterricht',

SUM(exam_lessons.duration) 'Prüfungen',

SUM(total_lessons.duration) 'angebotene Stunden gesamt',

SUM(activity_lessons.duration) 'Sonstige Tätigkeiten',

SUM(canceled_lessons.duration) 'abgesagt von 123 <48',

SUM(canceled_48_lessons.duration) 'abgesagt von 123 >48',

SUM(canceled_student_lessons.duration) 'abgesagt von Schueler <48',

SUM(canceled_48_student_lessons.duration) 'abgesagt von Schueler >48',

SUM(canceled_instructor_lessons.duration) 'abgesagt von FL <48',

SUM(canceled_48_instructor_lessons.duration) 'abgesagt von FL >48'

FROM

lesson

    LEFT JOIN

user ON lesson.instructor_id = user.id

    LEFT JOIN

school ON lesson.school_id = school.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NULL) AS offered_lessons ON offered_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving'

        AND lesson.user_id IS NOT NULL) AS booked_lessons ON booked_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'driving') AS total_week_lessons ON total_week_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = ('driving' OR 'theory' OR 'exam')) AS total_lessons ON total_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'theory') AS theory_lessons ON theory_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'exam') AS exam_lessons ON exam_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id, lesson.duration

FROM

    lesson

WHERE

    lesson.status = 'active'

        AND entity = 'activity') AS activity_lessons ON activity_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_lessons ON canceled_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id != lesson.cancelled_by_id) AS canceled_48_lessons ON canceled_48_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_student_lessons ON canceled_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND user_Id = lesson.cancelled_by_id) AS canceled_48_student_lessons ON canceled_48_student_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) < 48

        AND instructor_Id = lesson.cancelled_by_id) AS canceled_instructor_lessons ON canceled_instructor_lessons.id = lesson.id

    LEFT JOIN

(SELECT 

    lesson.id,

        lesson.duration,

        TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate)

FROM

    lesson

WHERE

    lesson.status = 'canceled'

        AND entity = 'driving'

        AND TIMESTAMPDIFF(HOUR, lesson.cancelled_at, lesson.startDate) >= 48

        AND Instructor_Id = lesson.cancelled_by_id) AS canceled_48_instructor_lessons ON canceled_48_instructor_lessons.id = lesson.id

WHERE

lesson.startDate >= '2018-01-31 00:00:00'

    AND lesson.startDate <= '2019-01-06 23:59:59'

GROUP BY lesson.instructor_id;


`
The code is almost identical. But now I get this error message:



Error 2



Can anyone help me out here?



Thanks in advance.






mysql mysql-workbench google-data-studio




mysql mysql-workbench google-data-studio






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 4 at 11:40







Jan Hoffmann

















asked Jan 4 at 11:13









Jan HoffmannJan Hoffmann

62




62













  • have you check google studio data source connecting with mysql

    – Kandy
    Jan 4 at 11:19



















  • have you check google studio data source connecting with mysql

    – Kandy
    Jan 4 at 11:19

















have you check google studio data source connecting with mysql

– Kandy
Jan 4 at 11:19





have you check google studio data source connecting with mysql

– Kandy
Jan 4 at 11:19












1 Answer
1






active

oldest

votes


















0














;

) AS t0 GROUP BY Vorname ORDER BY qt_bdgy5cbbub DESC'


Do you have a semicolon at the end of a sub query, just before 



") AS t0"?





share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54037881%2ferror-in-google-data-studio-when-using-mysql-as-data-source%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    ;
    
) AS t0 GROUP BY Vorname ORDER BY qt_bdgy5cbbub DESC'


    Do you have a semicolon at the end of a sub query, just before 



    ") AS t0"?





    share|improve this answer




























      0














      ;
      
) AS t0 GROUP BY Vorname ORDER BY qt_bdgy5cbbub DESC'


      Do you have a semicolon at the end of a sub query, just before 



      ") AS t0"?





      share|improve this answer


























        0












        0








        0







        ;
        
) AS t0 GROUP BY Vorname ORDER BY qt_bdgy5cbbub DESC'


        Do you have a semicolon at the end of a sub query, just before 



        ") AS t0"?





        share|improve this answer













        ;
        
) AS t0 GROUP BY Vorname ORDER BY qt_bdgy5cbbub DESC'


        Do you have a semicolon at the end of a sub query, just before 



        ") AS t0"?






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 8 at 22:23









        Pete MontgomeryPete Montgomery

        1




        1
































            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54037881%2ferror-in-google-data-studio-when-using-mysql-as-data-source%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Monofisismo

            Image
            Monofisismo (do grego: monos - "único, singular" e physis - "natureza") é o ponto de vista cristológico que defende que, depois da união do divino e do humano na encarnação histórica, Jesus Cristo, como encarnação do Filho ou Verbo ( Logos ) de Deus, teria apenas uma única "natureza", a divina, e não uma síntese de ambas. O monofisismo é contraposto pelo diofisismo (ou "diafisismo"), que defende que Jesus preservou em si as duas naturezas. Historicamente, o monofisismo se refere primordialmente à posição dos que (especialmente no Egito e, em menor grau, na Síria) rejeitaram as decisões do Concílio de Calcedônia em 451 (o quarto concílio ecumênico). Os membros mais moderados entre eles, porém, defendem a teologia "miafisita", que se tornou a oficial para as Igrejas Ortodoxas Orientais. Muitos ortodoxos orientais, porém, rejeitam essa classificação, mesmo como um termo genérico, mas ele é amplamente utilizado na literatura históri...
            Read more

            Angular Downloading a file using contenturl with Basic Authentication

            Image
            0 I have a contentUrl when user clicks on that,file should be downloaded. If I try to browse the contentUrl in the browser it asks for Userid and password.I believe this is Basic Authentication.I dont want user to enter UserId and Password so I am passing it Authorization(Please see below code). Url looks some thing like this: http://XXX.XXX.XX.XXX:8080/flowable-rest/service/runtime/tasks/90875/attachments/90555/content let username : string = 'admin'; let pwd : string = 'test'; let headers = new Headers(); headers.set('Accept', 'text/json'); headers.append('Content-Type', 'application/json'); headers.set('Access-Control-Allow-Origin', '*'); headers.set('Access-Control-Allow-Methods','POST, GET, OPTIONS, PUT, DELETE'); head...
            Read more

            Olmecas

            Image
            Monumento 1, uma das quatro cabeças colossais encontradas em La Venta, com altura aproximada de 3 metros Olmecas é a designação do povo e da civilização que estiveram na origem da antiga cultura pré-colombiana da Mesoamérica e que se desenvolveram nas regiões tropicais do centro-sul do atual México durante o pré-clássico, próximo de onde hoje estão localizados os estados mexicanos de Veracruz e Tabasco, no Istmo de Tehuantepec, numa zona designada área nuclear olmeca. A cultura olmeca floresceu nesta região aproximadamente entre 1500 e 400 a.C., [ 1 ] e crê-se que tenha sido a civilização-mãe de todas as civilizações mesoamericanas que se desenvolveram posteriormente. [ 2 ] No entanto, desconhece-se a sua exacta filiação étnica, ainda que existam numerosas hipóteses colocadas para tentar resolver esta questão. O etnónimo olmeca foi cunhado pelos arqueólogos do século XX, e não devem confundir-se com os muito posteriores olmecas-xicalancas que ocuparam vários locais do México...
            Read more