CODEWITHSUNDEEP
phpmysql
Alan Zelsi
Alan Zelsi

Reputation: 11

Sum values by date

I have read the tutorials, searched forums for 2 weeks, and not found the assistance I require. I now jump in to ask my questions...

I am new to this arena, so I honor your kindnes to my situation.

I have 2 Solar Inverters I have at my home, and I have a RS232 data port on each that is now connected to my home network via an IP converter to upload the data of the power they make each hour to a simple website I made, so I can see what is going on durring the hours I am at work. The information goes to my FTP site and the data falls into the table perfectly. I am trying to get the PHP and MySQL code to list the data into the forms/tables on the web page. For this project I have set up Dreamweaver and a test server on my home PC, to get this working. All the data on the web host MySQL is the same as in the test server (other then the host, name, and password but it works fine).

I have a basic MySQL Table...

+---------+----------+------------+---------+------------+----------+
| Unit ID |  Date    |    Time    |  Power  |  Volts     |  Current |
+---------+----------+------------+---------+------------+----------+
|    1    |YYYY-MM-DD|  12:00:00  |  560    |   XXXX     |   XXXX   |
+---------+----------+------------+---------+------------+----------+
|    1    |YYYY-MM-DD|  13:00:00  |  560    |   XXXX     |   XXXX   |
+---------+----------+------------+---------+------------+----------+
|    2    |YYYY-MM-DD|  12:00:00  |  490    |   XXXX     |   XXXX   |
+---------+----------+------------+---------+------------+----------+

Etc....

I am able to SUM the Total "Power" of all the "Power" Rows with...

<?php
$con = mysql_connect("localhost","NAME","PASWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("inverters", $con);
$qry = mysql_query(" SELECT SUM(Power) AS total FROM feed "); 
$row = mysql_fetch_assoc($qry); 
echo $row['total']; 
mysql_close($con);
?>

I have been trying for weeks to get "the current day power", the month power etc...

I have thought about having a "This Week", but lord only knows how long that would take... hahaha

As seen on the website.

+-----------+----------------+-----------------+-----------------+
| Today (W) |  This Month    |    This Year    |  Lifetime Power |  
+-----------+----------------+-----------------+-----------------+
|????????| ??????????|???????????|----working----| 
+-----------+----------------+-----------------+-----------------+

For the Current Day, I have tried this, but it does not work...

$query = mysql_query("SELECT SUM(Power) ORDER BY(Time) CURDATE(), AS 'day_total' FROM feed ");
$result = mysql_fetch_array($query);
$daytotal = $result["day_total"];
echo $row['day_total'];

For the YEAR, I have tried this, but it does not work...

$query = mysql_query("SELECT SUM(Power) AS year_total FROM feed WHERE YEAR(date) = YEAR(CURDATE());");
$result = mysql_fetch_array($query);
$yeartotal = $result["year_total"];

I have not even got to the month yet as I am reading and reading. Online I see how to get user names, and the time they register, and how many visit hits per year or month etc, but none have been informative to my situation. I think the more I read on the PHP and MySQL website, the more stupid I have become (as you can verywell see from the code above.

Using Google to seach how to get the Current Date only showes me how to enter the current date in a row, not how to SUM the values of the current day.

I would greatly honor any assistance to my learning and functioning of this.

Thank You,

Alan

Upvotes: 1

Views: 2691

Answers (2)

oezi
oezi

Reputation: 51797

sum power per day:

SELECT date, SUM(Power) AS total FROM feed GROUP BY date ORDER BY date

for month/week/year and so on, you just have to change the GROUP BY-clause

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881113

You can get daily totals with something like:

select mydate, sum(powerdrain)
from mytable
/* where date > now - 7 days */
group by date
order by date;

inserting, if need be, where clauses to further restrict the data (I'm not sure on the syntax of that one, you'll need to check).

The crux of the answer is the group by clause, which aggregates data into groups, surprisingly enough :-)

Upvotes: 0

Related Questions