For each new year from date straing PHP MySQL

Question

How can I create a result, in either PHP or MySQL which will show the year 2005, as example, and sum all results

Example input

-------------------------
Date       \    Quantiti
-------------------------
12-05-2005 \      5
-------------------------
23-08-2005 \      8
-------------------------
11-02-2006 \      4
-------------------------
26-09-2006 \      2
-------------------------

Example ouput

---------------------------
year    \    Total
---------------------------
2005    \     13
---------------------------
2006    \     6
---------------------------

<?php
$sql = "SELECT * FROM mytable ORDER BY date DESC"; 
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        $date = $row["date"];
        $quantiti = $row["quantiti"];

        // here i need code for each new year 
    }
    } else {


    }
?>

The big letters are for headers. Could you please edit your question and make it readable. — cpap, Apr 19 '17 at 07:39
You should take a look at `YEAR()` and `SUM()`: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html — jeroen, Apr 19 '17 at 07:39

score 1 · Answer 1 · answered Apr 19 '17 at 07:43

You can SUM() all the quantyties which have the same year by grouping the years, using GROUP BY YEAR(date). A simple query like this should do the job

SELECT SUM(Quantiti) as `total`, 
       YEAR(date) as `year` 
FROM mytable 
GROUP BY YEAR(date)

Note that the returning result is named total and year, as per your example output.

Simos Fasouliotis · Accepted Answer · 2017-04-19T09:19:42.930

0

You can use SUM to sumup the quantity and GROUP BY to group results by year.

A sql query for your needs would be:

SELECT YEAR(Date), SUM(Quantiti) 
FROM mytaple
GROUP BY YEAR(Date)

So your code would be:

<?php
$sql = "SELECT YEAR(Date) as y, SUM(Quantiti) as q FROM mytaple GROUP BY YEAR(Date)"; 
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        $date[] = $row["y"];
        $quantiti[] = $row["q"];
    }
for($i<0; $i<count($date);$i++) {
        echo $date[$i]."/".$quantiti[$i];
    }
    } else {


    }

    ?>

edited Apr 19 '17 at 09:19

answered Apr 19 '17 at 07:43

Simos Fasouliotis

1,383
2
16
35

Thanks the idea was 'GROUP BY'. But the issue is i don't know how to adapt it in my code in PHP :( – user2767046 Apr 19 '17 at 09:11
Thank you its working now, appreciate it – user2767046 Apr 19 '17 at 09:39

score -1 · Answer 3 · answered Apr 19 '17 at 07:47

-1

Group by With year and Month will achieve this

GROUP BY YEAR(Date), MONTH(Date)

answered Apr 19 '17 at 07:47

Nitin Rakesh Nagar

261
2
4

For each new year from date straing PHP MySQL

3 Answers3