question

nazone avatar image
nazone asked

How to Display the park names and total sales for Theme Parks who are located in the country ‘UK’ or ‘FR’ in Sql

@nazone please help me with my sql

joins
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

this is the erd databae

0 Likes 0 ·
MikeyBronowski avatar image
MikeyBronowski answered

I am assuming here the SALES_LINE table has all the details about amount and we do not need to calculate anything from TICKET table.

Would that work for you @nazone?

WITH CTESales (TRANSACTION_NO, Sales)  
AS  
(  
    SELECT TRANSACTION_NO, SUM(LINE_PRICE) Sales 
    FROM SALES_LINE
    GROUP BY TRANSACTION_NO 
),
CTESalesTotal (PAR_CODE,TotalSales)
AS
(
    SELECT S.PAR_CODE,SUM(CS.SALES) TotalSales
    FROM CTESales CS 
    JOIN SALES S ON S.TRANSACTION_NO = CS.TRANSACTION_NO
    GROUP BY S.PAR_CODE
)
SELECT P.PARK_NAME, P.PARK_COUNTRY, COALESCE(CST.TotalSales,0) AS TotalSales
FROM CTESalesTotal CST
RIGHT JOIN THEMEPARK P ON P.PARK_CODE = CST.PAR_CODE
WHERE P.PARK_COUNTRY IN ('FR','UK');


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

studentMAS avatar image
studentMAS answered

image-2022-05-16-230828278.pngis there any way to simplify the coding sir?is it like this,, can u fix this cuz we have to make simplify the coding for one question


10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.