CODEWITHSUNDEEP
sqlgoogle-bigquery
Mahima Lakra
Mahima Lakra

Reputation: 11

How to aggregate rows on BigQuery

I need to group different years in my dataset so that I can see the total number of login_log_id each year has(BigQuery)

SELECT login_log_id,
DATE(login_time) as login_date,
EXTRACT(YEAR FROM login_time) as login_year,
TIME(login_time) as login_time,
FROM `steel-time-347714.flex.logs`
GROUP BY login_log_id

I want to make a group by so that I can see total number of login_log_id generated in different years.

My columns are login_log_id, login_time

I am getting following error :-

SELECT list expression references column login_time which is neither grouped nor aggregated at [2:6]

Upvotes: 1

Views: 318

Answers (1)

Damião Martins
Damião Martins

Reputation: 1849

The error is because every column you refer to in the select need to be aggregated or be in the GROUP BY.

If you want the total logins by year, you can do:

SELECT 
  EXTRACT(YEAR FROM login_time) as login_year,
  COUNT(1) as total_logins,
  COUNT(DISTINCT login_log_id) as total_unique_logins
FROM `steel-time-347714.flex.logs`
GROUP BY login_year

But if you want the total by login_log_id and year:

SELECT 
  login_log_id,
  EXTRACT(YEAR FROM login_time) as login_year,
  COUNT(1) as total_logins
FROM `steel-time-347714.flex.logs`
GROUP BY login_log_id, login_year

Upvotes: 2

Related Questions