For each new year from date straing PHP MySQL [duplicate]

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
– cpap, Commented Apr 19, 2017 at 7:39
You should take a look at YEAR() and SUM(): dev.mysql.com/doc/refman/5.7/en/group-by-functions.html — jeroen
– jeroen, Commented Apr 19, 2017 at 7:39

Qirel · Accepted Answer · 2017-04-19 07:43:36Z

1

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.

answered Apr 19, 2017 at 7:43

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simos Fasouliotis · Accepted Answer · 2017-04-19 09:19:42Z

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, 2017 at 9:19

answered Apr 19, 2017 at 7:43

Simos Fasouliotis

1,3902 gold badges18 silver badges37 bronze badges

2 Comments

user2767046 Over a year ago

Thanks the idea was 'GROUP BY'. But the issue is i don't know how to adapt it in my code in PHP :(

user2767046 Over a year ago

Thank you its working now, appreciate it

Nitin Rakesh Nagar · Accepted Answer · 2017-04-19 07:47:23Z

-1

Group by With year and Month will achieve this

GROUP BY YEAR(Date), MONTH(Date)

answered Apr 19, 2017 at 7:47

Nitin Rakesh Nagar

2913 silver badges4 bronze badges

Collectives™ on Stack Overflow

For each new year from date straing PHP MySQL [duplicate]

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related