GROUP BY query in Google Data Studio

I have a table that has several columns, including a column for the amount of tickets sold and a sales time column,
I want to know how many tickets were sold at any given hour.

For example

Here is the query I wrote-

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT hour(time) as hour, tickets  FROM Showtimes_View
group by hour(time)
order by hour

The query ran well on my MySQL,

The problem is that when I try to run it in Google Data Studio, I get an error.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

skip the removing of full GROUP BY and use SUM as aggregation function

SELECT hour(time) as hour, SUM(tickets)  FROM Showtimes_View
group by hour(time)
order by hour

Method 2

Try using this version on Standard BigQuery:

SELECT EXTRACT(HOUR from time) AS hour, SUM(tickets) AS num_tickets
FROM Showtimes_View
GROUP BY 1
ORDER BY 1;

You remarked that The query ran well on my MySQL. The query may have ran, but turning off GROUP BY strict mode to make a query run usually isn’t best practice.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0