azim hamdan azim hamdan - 5 months ago 8
SQL Question

MYSQL & PHP : Sort data and group by day

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

Answer

Try this:

SELECT DATE_FORMAT(date_purchase,'%d/%m/%Y'), SUM(prod_price)
FROM $table
GROUP BY DATE_FORMAT(date_purchase,'%d/%m/%Y');
Comments