GROUP BY query in Google Data Studio

Question

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

time	tickets
10:45	5
10:30	6
10:15	3
10:00	2
11:14	8
11:30	6

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.

Answer 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

Answer 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.