CODEWITHSUNDEEP
google-bigquery
Brian Musfelt
Brian Musfelt

Reputation: 21

Ambiguous column name in BigQuery

I have this statement below. When I run it in BigQuery, I get an error stating that hours is an ambiuous column name. I'm fairly certain that all I need to do is state which table I'm getting hours(hours.icxmedia-servers:icx_metrics.issues_and_zenhub) from, but when I do that and group it, BigQuery says that it is an invalid group by. I think i'm close but I'm stuck and any suggestions will be super helpful 

SELECT
  repo,
  ticket_number,
  title,
  hours,
  assignee,
  state,
  pipeline,
  created_at,
  closed_at,
  points,
  quarter_closed,
  year_closed, 
  CASE
    WHEN state = 'Closed' 
    THEN sum(hours)
    ELSE hours
    END AS hours
FROM (
SELECT
  repo,
  ticket_number,
  title,
  assignee,
  state,
  pipeline,
  MAX(IF(closed_at IS NOT NULL, 0, MAX(ROUND((end_epoch-start_epoch)/3600,2)))) AS hours,
  MIN(created_at) AS created_at,
  MAX(closed_at) AS closed_at,
  MAX(points) AS points,
  QUARTER(closed_at) AS quarter_closed,
  YEAR(closed_at) AS year_closed
FROM
  [icxmedia-servers:icx_metrics.issues_and_zenhub] AS historical,
  (
  SELECT
    repository.name AS repo,
    IF(issue.number IS NOT NULL, issue.number, pull_request.number) AS ticket_number,
    FIRST(IF(issue.number IS NOT NULL, issue.title, pull_request.title)) AS title,
    IF(issue.number IS NOT NULL, issue.assignee.login, pull_request.assignee.login) AS assignee,
pipeline.name AS pipeline,
    IF(MAX(IF(IF(issue.number IS NOT NULL, issue.state, pull_request.state) == "open",0,1)) == 1, "closed","open") AS state,
    IF(issue.number IS NOT NULL, issue.created_at, pull_request.created_at) AS created_at,
    MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at)) AS closed_at,
    NULL AS assign_times,
    TIMESTAMP_TO_SEC(IF(issue.updated_at IS NOT NULL,issue.updated_at, pull_request.updated_at)) AS start_epoch,
    LEAD(start_epoch, 1) OVER (ORDER BY ticket_number, start_epoch ASC) AS end_epoch,
    MAX(estimate.value) AS points,
    QUARTER(MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at))) AS quarter_closed,
    YEAR(MAX(IF(issue.number IS NOT NULL, issue.closed_at, pull_request.closed_at))) AS year_closed
  FROM
    [icxmedia-servers:icx_metrics.gh_zh_data_production]
  WHERE
    issue.number IS NOT NULL
    OR pull_request.number IS NOT NULL
  GROUP BY
    repo,
    start_epoch,
    pipeline,
    ticket_number,
    created_at,
    assignee) AS prod
    WHERE title=="LinkedIn"

GROUP BY
  repo,
  ticket_number,
  title,
  assignee,
  pipeline,
  state,
  quarter_closed,
  year_closed
) A
GROUP BY      
repo,
ticket_number,
title,
assignee,
state,
pipeline,
created_at,
closed_at,
points,
quarter_closed,
year_closed

Upvotes: 2

Views: 15333

Answers (1)

Michael Entin
Michael Entin

Reputation: 7724

You have two fields called hours in a single SELECT, one is pass-through hours fields and another is a field calculated in CASE statement that is also called hours. Rename second one to avoid name collision.

SELECT
  ...
  hours,             <-- first 'hours'
  ...
  CASE
    WHEN state = 'Closed' 
    THEN sum(hours)
    ELSE hours
    END AS hours     <-- second 'hours'

Upvotes: 3

Related Questions