MYSQL count child table with other modifiers - help please!

Question

I have a ticket purchasing database and want to collect all of the orders that meet certain criteria, and then also grab the total number of tickets each order has by counting the rows in the 'orders_tickets' table that match the orderID. If I make the SQL a simple call, this SQL works:

SQL A:

SELECT o . * , COUNT( ot.OrderTicketID ) AS numtix
FROM orders o
LEFT JOIN orders_tickets ot ON o.orderID = ot.orderID
GROUP BY o.orderID
LIMIT 0 , 30

And this SQL works too - this is the original SQL call with all the data I want except the numtix data:

SQL B:

SELECT o.*,
IF(o.orderedbyID = '0', ob.fname, u.fname) AS obfname,
IF(o.orderedbyID = '0', ob.lname, u.lname) AS oblname
FROM orders o, perfs p, orders_orderedby ob, users u
WHERE p.eventID = '2'
AND p.perfID = o.perfID
AND ( (UNIX_TIMESTAMP(p.perfdatetime) - UNIX_TIMESTAMP(NOW())) > 0)
AND ob.orderID = o.orderID
AND u.userID = o.orderedbyID
ORDER BY p.perfdatetime ASC
LIMIT 0, 30

But when I try to incorporate SQL A into SQL B, I get errors:

SQL C: (does not work)

SELECT o.*, COUNT( ot.OrderTicketID ) AS numtix,
IF(o.orderedbyID = '0', ob.fname, u.fname) AS obfname,
IF(o.orderedbyID = '0', ob.lname, u.lname) AS oblname
FROM orders o, perfs p, orders_orderedby ob, users u
LEFT JOIN orders_tickets ot ON o.orderID = ot.orderID
WHERE p.eventID = '2'
AND p.perfID = o.perfID
AND ( (UNIX_TIMESTAMP(p.perfdatetime) - UNIX_TIMESTAMP(NOW())) > 0)
AND ob.orderID = o.orderID
AND u.userID = o.orderedbyID
ORDER BY p.perfdatetime ASC
GROUP BY o.orderID
LIMIT 0, 30

This is the error I get:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY o.orderID LIMIT 0, 30' at line 12

My instinct is that the "GROUP BY" needs to apply to the LEFT JOIN and not the entire SQL, but that's a sheer guess.

Any help is most appreciated!

Answer 1

ORDER BY goes after GROUP BY:

GROUP BY o.orderID
ORDER BY p.perfdatetime ASC
LIMIT 0, 30

Update:

SELECT  o.*,
        COUNT( ot.OrderTicketID ) AS numtix,
        IF(o.orderedbyID = '0', ob.fname, u.fname) AS obfname,
        IF(o.orderedbyID = '0', ob.lname, u.lname) AS oblname
FROM    orders o
JOIN    perfs p
ON      p.perfID = o.perfID
JOIN    orders_orderedby ob
ON      ob.orderID = o.orderID
JOIN    users u
ON      u.userID = o.orderedbyID
LEFT JOIN
        orders_tickets ot
ON      ot.orderID = o.orderID
WHERE   p.eventID = '2'
        AND p.perfdatetime < NOW()
GROUP BY
        o.orderID
ORDER BY
        p.perfdatetime ASC
LIMIT 0, 30