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.

CodePudding user response：

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

CodePudding user response：

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.