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.