2

tbl_orders

order_id, qty
123, 2
124, 1
125, 3
126, 1
127, 1
128, 1
129, 4

tbl_score

order_id, name, created
123, product 1, 2018-06-01
124, product 1, 2018-06-01
125, product 1, 2018-06-02
126, product 3, 2018-06-02
127, product 2, 2018-06-03
128, product 3, 2018-06-03
129, product 3, 2018-06-03

Required output

             2018-06-01       2018-06-02        2018-06-03
product 1        3                  3               0
product 2        0                  0               1
product 3        0                  1               5

The following query produces the required output for 2018-06-01 but takes around 1 min and 45 seconds to complete on the live database. If I add more dates using case it takes more time. Can someone help me fine tune it or perhaps suggest another solution?

select s.name, 
  sum( case when date(s.created) = '2018-06-01' then o.qty else 0 end ) as 'June 1'  
from tbl_score s  inner join tbl_order o 
 on s.order_id=o.order_id group by s.name;
  • The schema does not make sense to me. It seems like the date should be in tbl_orders, on the assumption that it is the date that the order was placed. – Rick James Jun 28 '18 at 17:57
  • @RickJames if the date field was on tbl_orders how would the query look like? Appreciate your inputs. – Anand Shah Jul 2 '18 at 12:30
3

Pivoting should be broken into two steps.

Step 1: Create a SELECT with the data not yet pivoted:

SELECT product, date, qty
    FROM ...

In your case, that query is likely to have a JOIN. (I am uncomfortable with which table date is in, but that is a separate issue.)

Step 2: Do the Pivoting.

SELECT product,
       ( ... ) AS '2018-06-01',
       ( ... ) AS '2018-06-02',
       ...
    FROM ( the-above-SELECT ) AS x

There could be issues with step 2 -- As shown, it requires a fixed number of columns. If you have a variable number of columns, you cannot build it this way, but need to construct the SELECT dynamically, then 'execute' it.

More discussion: http://mysql.rjweb.org/doc.php/pivot

  • Thank you Rick, this gives me a head start and hopefully a solution. – Anand Shah Jul 3 '18 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.