CODEWITHSUNDEEP
phpmysqlsqldate
ietax
ietax

Reputation: 89

PHP and MYSQL calculate total of month and copy it to another table

I have table (orders): id, price, date

id  |  price  |   date

01  |  250    |  2013-01-25

02  |  350    |  2013-01-25

03  |  25     |  2013-02-03

04  |  14     |  2013-03-21

05  |  96     |  2013-08-12

06  |  37     |  2013-08-12

07  |  89     |  2013-08-28

08  |  78     |  2013-12-20

09  |  45     |  2013-12-21

I want to output everytime that I execute scrip:

I'd like to insert all days total in another table like this one with all year days (for ex.):

date        |  price

2013-01-01  |  5656

2013-01-02  |  659596

2013-01-03  |  5464

For day total I used this code

...ecc..

$date = date("Y-m-d");

....ecc...

$query = "SELECT id, price FROM orders WHERE delivery_date LIKE '%$date%'";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result))

...ecc...

but I can do it only for current date, not for any daye. And I cannot do it for anty month..

Upvotes: 1

Views: 1436

Answers (1)

Filipe Silva
Filipe Silva

Reputation: 21657

You seem to be looking for a SUM and GROUP BY:

To get the SUM of prices for each day you can do:

SELECT date, SUM(price) as price
FROM orders 
GROUP BY date
ORDER BY date;

sqlfiddle demo

This will get you the date and the total sum of prices for that date, as long as there is any item in that date.

To insert it into another table you can do:

INSERT INTO tab2
SELECT date, SUM(price) as price
FROM orders 
GROUP BY date

Upvotes: 2

Related Questions