SQL query to generate the following output

Display user id, user name, total amount, amount to be paid after discount and give alias name as User_ID, user_name, Total_amount, Paid_amount. Display record in descending order by user id. Click on TABLE SHCEMA to get the table

This is the code I’ve written. But this is not showing the expected result:

SELECT 
    USERS.USER_ID AS User_ID,
    NAME AS user_name,
    (FARE * NO_SEATS) AS Total_amount 
FROM 
    USERS 
INNER JOIN 
    TICKETS ON USERS.USER_ID = TICKETS.USER_ID 
INNER JOIN 
    PAYMENTS ON TICKETS.TICKET_ID = PAYMENTS.TICKET_ID 
INNER JOIN 
    DISCOUNTS ON PAYMENTS.DISCOUNT_ID = DISCOUNTS.DISCOUNT_ID 
GROUP BY 
    USERS.USER_ID, NAME, FARE, NO_SEATS
ORDER BY 
    USERS.USER_ID DESC;

This is the expected output:

USER_ID USER_NAME   TOTAL_AMOUNT   PAID_AMOUNT
----------------------------------------------
 5       Krena           700         625
 4       Johan           800         775
 3       Ivan           3000        2900
 1       John           4000        3950
 1       John           4000        3950
 1       John           2000        1900

Answer

This will work, try it

SELECT 
USERS.USER_ID AS USER_ID,
USERS.NAME AS USER_NAME,
(TICKETS.FARE * TICKETS.NO_SEATS) AS TOTAL_AMOUNT,
(TICKETS.FARE * TICKETS.NO_SEATS - DISCOUNTS.DISCOUNT_AMOUNT) AS PAID_AMOUNT 
FROM 
USERS 
INNER JOIN TICKETS 
        ON USERS.USER_ID=TICKETS.USER_ID 
INNER JOIN PAYMENTS
        ON TICKETS.TICKET_ID=PAYMENTS.TICKET_ID 
INNER JOIN DISCOUNTS
        ON PAYMENTS.DISCOUNT_ID=DISCOUNTS.DISCOUNT_ID 
ORDER BY USERS.USER_ID DESC;