CODEWITHSUNDEEP
sqlgoogle-bigquery
stacker
stacker

Reputation: 85

SELECT list expression references column user_id which is neither grouped nor aggregated at [8:5]

I have 2 data sets. One of all patients who got ill (endo-2) and one of a special group of patients that also exists in endo-2 called "xp-56"

I've been trying to run this query and I'm not sure why it isn't working. I want to do counts of 3 columns in endo-2 of those patients that belong in the xp-56 table.

this is the code I've been using with the following error

SELECT list expression references column user_id which is neither grouped nor aggregated at [8:5]

how do I fix this so I never make the same mistake again!

SELECT
 Virus_Exposure,
 Medical_Delivery,
 Number_of_Site
 FROM 
 (
  SELECT
  medical_id,
  COUNT(DISTINCT Virus_id) AS Virus_Exposure,
  COUNT(EndoCrin_id) AS Medical_Delivery,
  COUNT (site_id_clinic) AS Number_of_Site
   FROM
   `endo-2`
    WHERE
   _PARTITIONTIME BETWEEN TIMESTAMP("2017-12-15")
    AND TIMESTAMP("2018-01-10")) AS a

RIGHT JOIN 

(
 SELECT
 medical_id
 FROM
 `xp-56`
 ORDER BY
   medical_id DESC) AS b

ON
a.medical_id=b.medical_id

GROUP BY
medical_id

Why doesnt the medical_id in table a work?

Upvotes: 2

Views: 5986

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Why not just do this?

SELECT e.medical_id,
       COUNT(DISTINCT e.Virus_id) AS Virus_Exposure,
       COUNT(e.EndoCrin_id) AS Medical_Delivery,
       COUNT(e.site_id_clinic) AS Number_of_Site
FROM `endo-2` e JOIN
     `xp-56` x
     ON x.medical_id = e.medical_id
WHERE e._PARTITIONTIME BETWEEN TIMESTAMP("2017-12-15") AND TIMESTAMP("2018-01-10")
GROUP BY e.medical_id;

Upvotes: 1

Related Questions