CODEWITHSUNDEEP
phpmysqlsql
user4540334
user4540334

Reputation:

sql - count records per weeks using timestamp

I have the table named 'tasaciones' which has 2 columns 'record' and 'fecha' (fecha is timestamp).

record  | fecha
-----------------------------

record1 | 2015-10-09 11:24:52

record1 | 2015-11-09 11:31:52

record2 | 2015-17-09 11:37:17

record3 | 2015-25-09 12:03:31

record3 | 2015-26-09 12:03:31

record4 | 2015-10-10 12:23:25

record4 | 2015-11-10 12:27:25

I need to make weekly report by counting records per weeks from the date of first record until the current time. I found something like this but even if this is what I need, I don't know how to write 'while' to echo the data.

SELECT year(fecha), datepart(week, fecha), count(*)
FROM tasaciones
GROUP BY year(fecha), datepart(week, fecha)

I want to get results like this:

week 1: 15 records

week 2: 10 records

week 3: 25 records

week 4: 25 records

Upvotes: 4

Views: 11809

Answers (4)

Suchit kumar
Suchit kumar

Reputation: 11859

There is no DATEPART in mysql try using week instead.Do not use * unnecessarily in count it will slow down your sql.

$query = "SELECT week(fecha) as w, count(record1) as cnt FROM tasaciones GROUP BY  week(fecha)";

    $res = $mysqli->query($query);
$table="<table><thead><th>WEEK</th><th>RECORDS</th></thead><tbody>";
    while ($results = $res->fetch_assoc()){
      $table .="<tr><td> Week:" . $results['w'] . "</td><td>" . $results['cnt'] . "records</td></tr>";
    }

 $table .="</tbody></table>";
echo $table;

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

There is no DATEPART() in MySQL.. You can use YEARWEEK() instead. So your query should look something like this.

SELECT count(*) as numRecords, YEARWEEK(fecha) as weekNum
FROM table
GROUP BY YEARWEEK(fecha)

Echoing out the results depends on the api you are using.

PDO:

$query = $SQL->prepare('...query here...');
$query->execute();

while ($row = $query->fetch(PDO::FETCH_ASSOC))
{
    echo $row['tags'];
}

MYSQLI:

$qry = '...query here...';
$results = $mysqli->query($qry);
while($row = $results->fetch_assoc()){
    echo $row['numRecords'], $row['weekNum']
}

Note: I didn't post a way to do this with the Mysql_ api since that has been deprecated and is vunerable to sql injection along with a whole lot of other problems. You should switch to PDO or Mysqli_ if you haven't already

Upvotes: 7

sandeepsure
sandeepsure

Reputation: 1115

Below is the code snippet for your question.

$sql = "SELECT year(fecha), datepart(week, fecha) as week, count(*) as week_total_record
FROM tasaciones
GROUP BY year(fecha), datepart(week, fecha)";

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
   echo $row['week']." : ".$row['week_total_record'];
}

Upvotes: -1

LeCoco
LeCoco

Reputation: 61

Concerning the first part of your question, your query should look something like this:

SELECT year(fecha), datepart('week',fecha), count(*)
FROM tasaciones
GROUP BY year(fecha), datepart('week',fecha)
WHERE fecha <= timestamp(now())

Concerning the second part of your question (i.e. "how to write 'while' to echo the data"), I guess you mean how to print the data from your query in PHP. So, assuming you're using PDO: 

$query = "SELECT year(fecha), datepart('week',fecha), count(*) FROM tasaciones GROUP BY year(fecha), datepart('week',fecha) WHERE fecha <= timestamp(now())";
$sql = $db->query($sql);
while ($results = $result->fetch()){
  echo $results[0] . ' ' . $results[1] . ' : ' . $results[2] . 'records.<br />';
}

Upvotes: -1

Related Questions