CODEWITHSUNDEEP
google-bigquery
Garrett
Garrett

Reputation: 401

How to average the year MM-DD-YYYY string of all rows in SQL BigQuery?

Context

This table was created in BigQuery from a Firestore database export storage bucket.

Goal

I am trying to create a query that averages the year of all my users in my user's table.

users table:

uid    username    birthday
XXXX   user1       04-04-2004

Attempts

SELECT 
    AVG( FORMAT_DATETIME("%y", birthday) )
FROM `bucket-96863.sqlQueries.users`
WHERE birthday != 'null' limit 10

-- Error: No matching signature for function FORMAT_DATETIME for argument types: STRING, STRING. Supported signatures: FORMAT_DATETIME(STRING, DATETIME); FORMAT_DATETIME(STRING, TIMESTAMP, [STRING]) at [2:10]

SELECT 
    AVG( CAST( EXTRACT(YEAR FROM birthday) AS int64 ) )
FROM `bucket-96863.sqlQueries.users`
WHERE birthday != 'null' limit 10

-- Error: No matching signature for function EXTRACT for argument types: DATE_TIME_PART FROM STRING. Supported signatures: EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); EXTRACT(DATE_TIME_PART FROM DATETIME); EXTRACT(DATE_TIME_PART FROM TIME) at [2:16]

Upvotes: 1

Views: 27

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

consider below

select avg(extract(year from cast(birthday as date format 'MM-DD-YYYY')))
from `bucket-96863.sqlQueries.users`

Upvotes: 1

Related Questions