Query for making sale report

Question

I have database schema of order_lines as

product_id, quantity, created_at

I want to query result like

date               1 2 3 4 5 ..
product 1 count    2 ...  
product 2 count    5 ...

Is it possible to do this in mySQL ?

Answer 1

Yes, I a cross tab query should do the trick.

This might be helpful: http://forums.mysql.com/read.php?20,110464,110464

http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html

Answer 2

select product_id, sum(quantity) from order_lines group_by product_id

Answer 3

Here is a possible solution. You may need to modify it to suit your needs:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`ordertotals` $$
CREATE PROCEDURE `test`.`ordertotals` ()
BEGIN
  DECLARE `xtab_qry` VARCHAR(4096) DEFAULT '';

  SELECT CONCAT('SELECT o.product_id ',
                 GROUP_CONCAT(
                     DISTINCT
                     CONCAT(', SUM(IF(o.created_at="', ol.created_at,
                            '",o.quantity,null)) AS "',
                            DATE_FORMAT(ol.created_at, "%m/%d/%Y"),
                            '"')
                      ORDER BY ol.created_at
                      SEPARATOR ''
                     ),
                     ' FROM orderlines o WHERE 1 GROUP BY o.product_id'
             ) AS stuff
        INTO
            @xtab_qry
        FROM
            orderlines ol
        WHERE 1;

        PREPARE my_sql_statement
        FROM @xtab_qry;

        EXECUTE my_sql_statement;

        DEALLOCATE
        PREPARE my_sql_statement;
END $$

DELIMITER ;

With this test data:

product_id  quantity created_at
1           4         2009-09-14
1           5         2009-09-14
2           2         2009-09-14
3           3         2009-09-13
1           9         2009-09-15
1           2         2009-09-16

It produced these results:

product_id  09/13/2009 09/14/2009 09/15/2009 09/16/2009
1                    9         9         2
2                    2  
3           3

Hope this helps!

EDIT: In summary, what this does is to create a stored procedure that generates a dynamic SQL string. It then executes that query. This is why this needs to be in a stored procedure. indiecompanyllc pointed to a great article that I used a while ago to learn how to do this. Here is that link: http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html.