Calculate ticket sales by using SUM in SELECT clause

Question

In this ticketing setup on dbfiddle, I am trying to calculate the ticket sales for a particular event by multiplying the ticket price and the total number of tickets sold for that event, based on their type i.e. standard, VIP.

So far I have this query,

SELECT tt.ticket_type,COUNT(tt.ticket_type) AS 'tickets_paid' , SUM((COUNT(tt.ticket_type)) *tc.price) 
FROM tbl_tickets t INNER JOIN tbl_ticket_count tc ON t.ticket_count_id = tc.ticket_count_id 
INNER JOIN tbl_events_new e ON tc.event_id = e.event_id 
INNER JOIN tbl_ticket_type tt ON tt.ticket_type_id = tc.ticket_type_id
WHERE e.event_id = 164 AND t.pay_status ='paid' GROUP BY tt.ticket_type

but I keep getting 'invalid use of group function - 1111' error after adding the multiplication part of the clause. How can I carry out this operation with the SUM function using the ticket price of type decimal and number of tickets sold?

Answer 1

Try this:

SELECT 
A.ticket_type_id, 
A.tickets_paid, 
A.tickets_paid*B.price as amount_paid
FROM
(SELECT tt.ticket_type_id,
COUNT(tt.ticket_type_id) AS tickets_paid 
FROM tbl_tickets t 
    INNER JOIN tbl_ticket_count tc 
        ON t.ticket_count_id = tc.ticket_count_id 
    INNER JOIN tbl_events_new e 
        ON tc.event_id = e.event_id 
    INNER JOIN tbl_ticket_type tt ON 
        tt.ticket_type_id = tc.ticket_type_id
WHERE 
    e.event_id = 164 
    AND t.pay_status ='paid' 
GROUP BY tt.ticket_type_id) as A
INNER JOIN tbl_ticket_count B ON 
        A.ticket_type_id = B.ticket_type_id

Answer 2

After a few hours of testing the previous answer, I decided to cut out the subquery as it was producing duplicate results in the set and simply multiply from the COUNT of the ticket totals like so:

SELECT tt.ticket_type,tc.price,
        COUNT(tt.ticket_type_id) AS tickets_paid_count ,(COUNT(tt.ticket_type_id) * tc.price) AS tickets_paid_sales
FROM tbl_tickets t 
    INNER JOIN tbl_ticket_count tc ON t.ticket_count_id = tc.ticket_count_id
    INNER JOIN tbl_events_new e ON tc.event_id = e.event_id 
    INNER JOIN tbl_ticket_type tt ON tt.ticket_type_id = tc.ticket_type_id
WHERE 
    e.event_id = '$eid' AND t.pay_status ='paid' 
GROUP BY tt.ticket_type,tc.price

Where the variable $eid is passed to the PHP script.