Help with SUBQUERY

I am trying to extract data from a POS database and I have the following code:

SELECT DATE_FORMAT(DATE_ADD(DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), INTERVAL (5*60 + 30) MINUTE), '%Y-%m-%d') AS interval_start,
CONVERT(EXTRACTVALUE(PRODUCTS.ATTRIBUTES, '/properties//entry[@key=\"maincat\"]') USING UTF8) AS MAINCAT,
ROUND(SUM(TICKETLINES.PRICE * TICKETLINES.UNITS), -3) AS DAYSALES
FROM TICKETLINES, TICKETS, RECEIPTS, PRODUCTS
WHERE TICKETLINES.PRODUCT = PRODUCTS.ID
AND TICKETLINES.TICKET = TICKETS.ID
AND TICKETS.ID = RECEIPTS.ID
AND TICKETLINES.PRODUCT IS NOT NULL
AND (receipts.datenew >= DATE_SUB(NOW(), INTERVAL 3 MONTH) AND receipts.person > 0)
GROUP BY DATE(receipts.datenew - INTERVAL (5*60 + 30) MINUTE), MAINCAT
ORDER BY interval_start, MAINCAT

which produces:
interval_start MAINCAT DAYSALES
2016-12-31 Drink 45108000
2016-12-31 Food 49791000
2016-12-31 Other 109000
2017-01-01 Drink 14226000
2017-01-01 Food 27425000
2017-01-01 Other 36000
2017-01-02 Drink 20400000
2017-01-02 Food 25648000
2017-01-02 Other 109000

The issue is that I need it in this format:
interval_start Drink Food
2016-12-31 45108000 36000
2016-12-31 49791000 20400000
2016-12-31 109000 25648000
2017-01-01 27425000 109000
2017-01-01 14226000 49791000

I believe I need to use a subquery to accomplish this, but I cannot make it work, any advice is appreciated.

 

Do you have separate fields in the database table for food and drink? If not, you can't get separate returns. (Subqueries can't create data that you didn't store.) If you do, just split the "MAINCAT" field query into 2 fields - FOOD and DRINK. If you don't, you'll have to add a field to the table, then start storing FOOD and DRINK as separate items.