# Calculate ticket sales by using SUM in SELECT clause

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?

• what is the relationship between tbl_tickets and tbl_ticket_count? would you put some data of each table in your question?
– AMG
Commented Aug 14, 2018 at 20:44
• I've added the test data; dbfiddle.uk/… Commented Aug 14, 2018 at 20:58
• @AMG the relationship between the two is the ticket_count keeps track of the number of tickets per type e.g. standard, VIP per event. so when you get a ticket, the ticket table keeps track of the pool/ticket_count which it was purchased from Commented Aug 14, 2018 at 20:59
• check my answer... I hope it helps
– AMG
Commented Aug 14, 2018 at 21:04

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
``````
• cant get it to run on MySQL, the subqueries are causing problems Commented Aug 14, 2018 at 21:07
• I applied some corrections, please try again
– AMG
Commented Aug 14, 2018 at 21:09
• the idea is to summarize using the ticket type and then apply the price to the sumarized result set
– AMG
Commented Aug 14, 2018 at 21:11
• the query ran but the results were not correct. it duplicated the two ticket types(std and vip). the multiplication result from the duplicates is wrong and the other is right fro each ticket type. strange Commented Aug 14, 2018 at 21:14
• well, you have now the way to make the query run. Now you just need to tune the results (filters and joins)... good luck!
– AMG
Commented Aug 14, 2018 at 21:20

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.