Query error: Column name ICUSTAY_ID is ambiguous. Using multiple subqueries in BigQuery

Hi, I receive the following query error “Query error: Column name ICUSTAY_ID is ambiguous” referred to the third last line of code (see the following code). Please can you help me? Thank you so much!

I am an SQL beginner..

WITH t AS 
(
SELECT 
*
FROM
    (
    SELECT *,
DATETIME_DIFF(CHARTTIME, INTIME, MINUTE) AS pi_recorded 
FROM 
        (
        SELECT 
        * 
        FROM 
            (
            SELECT * FROM
                (SELECT i.SUBJECT_ID, p.dob, i.hadm_id, p.GENDER, a.ETHNICITY, a.ADMITTIME, a.INSURANCE, i.ICUSTAY_ID,
                i.DBSOURCE, i.INTIME, DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) AS age, 
                    CASE 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) <= 32485  
                    THEN 'adult' 
                WHEN DATETIME_DIFF(a.ADMITTIME, p.DOB, DAY) > 32485  
                    then '>89'
                END AS age_group
                FROM `project.mimic3.ICUSTAYS` AS i
                INNER JOIN `project.mimic3.PATIENTS` AS p ON i.SUBJECT_ID = p.SUBJECT_ID  
                INNER JOIN `project.mimic3.ADMISSIONS` AS a ON i.HADM_ID = a.HADM_ID)
                WHERE age >= 6570
            ) AS t1
                LEFT JOIN 
                (
                SELECT ITEMID, ICUSTAY_ID, CHARTTIME, VALUE, FROM `project.mimic3.CHARTEVENTS` 
                WHERE ITEMID = 551 OR ITEMID = 552 OR ITEMID = 553 OR ITEMID = 224631 
                OR ITEMID = 224965 OR ITEMID = 224966
                ) AS t2
                ON t1.ICUSTAY_ID = t2.ICUSTAY_ID
        )
    ) 
WHERE ITEMID IN (552, 553, 224965, 224966) AND pi_recorded <= 1440
)
SELECT ICUSTAY_ID      #### Query error: Column name ICUSTAY_ID is ambiguous 
FROM t  
GROUP BY ICUSTAY_ID;

Answer

Both t1 and t2 have a column called ICUSTAY_ID. When you join them together into a single dataset you end up with 2 columns with the same name – which obviously can’t work as there would be no way of uniquely identify each column.

You need to alias these columns in you code or not include one or the other if you don’t need both