Search code examples
sqlmysqlselectdistinct

MYSQL query select distinct month and year

I have a table with a datetime column with multiple dates in. I want to output the month and year for every row that exists only once. e.g "April 2017, April 2018, May 2018, June 2018"

I've managed to get the below working, this displays "April May June", but it needs to display April twice as it is in different years and I also want to display their respective years. 

<?php $results = mysqli_query($link, 'SELECT DISTINCT MONTH(date_added) AS "Month" FROM payments') or die("Query fail: " . mysqli_error($link)); ?>

<?php foreach($results as $result) :?>
    <?php $monthText = date("F", strtotime("2001-" . $result['Month'] . "-01")); ?>
            <?= $monthText ?>
<?php endforeach; ?>

I've been researching for hours and not manged to find a solution that works.

Solution

  • You can apply the distinct keyword to a combination of fields:

    SELECT DISTINCT YEAR(date_added) AS "Year", MONTH(date_added) AS "Month" FROM payments