question

Nan-7114 avatar image
0 Votes"
Nan-7114 asked BertZhoumsft-7490 edited

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

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 ;

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@Nan-7114

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.