-2

Currently I have this meta data which are prod_price and date_purchase (TIMESTAMP), I would like to have like this report:

Data
Record 1(prod_price:500 , date_purchase:2015-07-08 21:40:15)
Record 2(prod_price:500 , date_purchase:2015-07-08 21:47:15)
Record 3(prod_price:1000 , date_purchase:2015-07-09 21:23:15)
Record 4(prod_price:1000 , date_purchase:2015-07-09 21:27:15)
Record 5(prod_price:1500 , date_purchase:2015-07-09 21:30:15)
Record 6(prod_price:500 , date_purchase:2015-07-09 21:53:15)

Report
***********************************************
Date ********** Total Sales
***********************************************
08/07/2015 ***** $1000
09/07/2015 ***** $4000



Thanks in advance

Hunter Turner
  • 6,804
  • 11
  • 41
  • 56
azim hamdan
  • 113
  • 10
  • can you show us what you've tried? – DevDonkey Jul 09 '15 at 15:19
  • I am new in SQL but I did my research on previous post, but then it not meet my question, http://stackoverflow.com/questions/9698977/group-by-month-of-unix-timestamp-field , http://stackoverflow.com/questions/1658340/sql-query-to-group-by-day, thanks need your advice – azim hamdan Jul 09 '15 at 15:21

1 Answers1

0

Try this:

SELECT DATE_FORMAT(date_purchase,'%d/%m/%Y'), SUM(prod_price)
FROM $table
GROUP BY DATE_FORMAT(date_purchase,'%d/%m/%Y');
MaggsWeb
  • 3,018
  • 1
  • 13
  • 23
  • 2
    Why should the OP "try this"? Please add an explanation of what you did and why you did it that way, not only for the OP but for future visitors to SO. – Jay Blanchard Jul 09 '15 at 15:21
  • @JayBlanchard because I wouldn't be so presumtious to presume that a solution will work perfectly first time, based on such a small snippet of code. If you 'Try this' and it works - Great! Otherwise, there will be the learning curve of a suggestion for the OP to encourage them to research and understand the suggestion - not just copy-n-paste someone elses answer that just happened to 'fix it' – MaggsWeb Jul 09 '15 at 15:25
  • 1
    If they "try this" and it works but they don't know why they still have the learning curve of clarifying what was done. A *good* answer includes an explanation even if the spirit of the answer is a "try this". It has nothing to do with presumption. – Jay Blanchard Jul 09 '15 at 15:28
  • @JayBlanchard Noted. Thankyou. – MaggsWeb Jul 09 '15 at 15:29
  • I just wondering why my post is frequently down voting, I saw other post is very simple and no research at all but can got high voting as this example, http://stackoverflow.com/questions/1658340/sql-query-to-group-by-day – azim hamdan Jul 09 '15 at 15:32