1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with SUBQUERY

Discussion in 'MySQL' started by Allan Giercke, Apr 1, 2017.

  1. #1
    I am trying to extract data from a POS database and I have the following code:
    SEMrush
    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.
     
    Allan Giercke, Apr 1, 2017 IP
    SEMrush
  2. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #2
    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.
     
    Rukbat, Apr 9, 2017 IP