I need to produce the below output using MySQL, which can calculate the total tickets available and the total tickets sold for each event;
event_id event_name start_date tickets_available tickets_sold
-------------------------------------------------------------------------
1 Test Event 1 2017-04-01 200 20
2 Test Event 2 2017-04-25 100 30
My tables are as follows;
Table: events
event_id user_id event_name start_date event_active
-------------------------------------------------------------------------
1 47 Test Event 1 2017-04-01 0
2 47 Test Event 2 2017-04-25 0
Table: ticket_types
ticket_type_id event_id ticket_type quantity_released quantity_remaining
---------------------------------------------------------------------------
1 1 General 100 90
2 1 VIP 100 90
3 2 General 50 30
4 2 VIP 50 40
Here's my SQL query so far;
SELECT events.event_id, events.event_name, events.start_date, events.event_active,
(
SELECT SUM(ticket_types.quantity_released)
FROM ticket_types
WHERE ticket_types.event_id = events.event_id
) AS tickets_available,
(
SELECT (ticket_types.quantity_released - ticket_types.quantity_remaining)
FROM ticket_types
WHERE ticket_types.event_id = events.event_id
) AS tickets_sold
FROM events
INNER JOIN ticket_types
ON events.event_id=ticket_types.event_id
WHERE user_id = 47;
However, this is not working as the logic for the columns tickets_available and tickets_sold is incorrect.
Can someone help me out with this query please?
SELECT EVENTS.EVENT_ID
, EVENTS.EVENT_NAME
, EVENTS.START_DATE
, SUM(TICKET_TYPES.QUANTITY_RELEASED) AS TICKETS_AVAILABLE
, SUM(TICKET_TYPES.QUANTITY_RELEASED) - SUM(TICKET_TYPES.QUANTITY_REMAINING) AS TICKETS_SOLD
FROM EVENTS
INNER JOIN TICKET_TYPES
ON EVENTS.EVENT_ID = TICKET_TYPES.EVENT_ID
GROUP BY EVENTS.EVENT_ID
, EVENTS.EVENT_NAME
, EVENTS.START_DATE;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments