Each GROUP BY expression must contain at least one column that is not an outer reference.

Question

WITH transport AS (
SELECT --top 5
date_id,booking_code ,city_id,taxi_type_id,driver_id
FROM dwh.f_transport
WHERE date_id = 20220321
),
rating AS (
SELECT date_id, driver_id,booking_code,response ,dax_rating ,response_type
FROM dwh.f_ratings
WHERE date_id = 20220321
)
SELECT
c.city_name
,tt.taxi_type_simple as vertical_name
,tt.taxi_type_name
,r.dax_rating
,r.response
,d.activated as driver_activated
,COUNT(DISTINCT t.booking_code) as total_responses
,r.driver_id
FROM transport t
INNER JOIN rating r
ON t.booking_code = r.booking_code
INNER JOIN dwh.d_city c
ON c.city_id = t.city_id
INNER JOIN dwh.d_taxi_type tt
ON t.taxi_type_id = tt.taxi_type_id
INNER JOIN dwh.d_driver d
ON d.driver_id = t.driver_id
WHERE r.date_id= 20220321
AND r.response_type in ('NEW_IMPROVEMENT' ,'IMPROVEMENT')
AND r.dax_rating < 5
AND c.country_id = 9
GROUP BY 1,2,3,4,5,6,8 ;

Answer 1

Instead of using numbers in the GROUP BY list every column by name. I also suggest to move r conditions inside the rating CTE, No need to repeat date condition the second time either.

Answer 2

Hi，@Nan

Welcome to Microsoft T-SQL Q&A Forum!

I can't understand why you're using two unrelated cte's, I'm guessing what you're trying to achieve is simply to link two tables, try the code below, and if something goes wrong please comment your expected result.

SELECT c.city_name,tt.taxi_type_simple as vertical_name,tt.taxi_type_name,  
       r.dax_rating,r.response,d.activated as driver_activated,  
	   COUNT(DISTINCT t.booking_code) as total_responses,r.driver_id  
FROM   dwh.f_transport t INNER JOIN dwh.f_ratings r  
ON     r.booking_code=t.booking_code   
       INNER JOIN dwh.d_city c  
ON     c.city_id = t.city_id  
       INNER JOIN dwh.d_taxi_type tt  
ON     tt.taxi_type_id=t.taxi_type_id   
       INNER JOIN dwh.d_driver d  
ON     d.driver_id = t.driver_id  
WHERE  r.date_id= 20220321 AND r.response_type in ('NEW_IMPROVEMENT' ,'IMPROVEMENT')  
       AND r.dax_rating < 5  
       AND c.country_id = 9  
GROUP BY c.city_name,tt.taxi_type_simple ,tt.taxi_type_name,  
       r.dax_rating,r.response,d.activated ,r.driver_id  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.