Query to group month,year, amount

Question

I'm trying to use a MYSQL query to group each month and each year and the total made in that month, i have tried various times but it always seems error out.

My current query:

<table class="table table-condensed table-striped table-hover table-responsive">
    <thead>
    <tr>
        <th>Month</th>
        <th>Year</th>
        <th>Amount</th>     
    </tr>
    </thead>
    <tbody> 
       <?php                
       $p1 = DB::getInstance()->select("SELECT SUM(`pay_cost`) AS `madePerMonth` MONTH(pay_date) as month, YEAR(pay_date) as year FROM `payments` GROUP BY YEAR(pay_date), MONTH(pay_date) DESC");
       ?>
       <?php foreach ($p1 as $r1) { ?>  
        <tr>
            <td><b><?php echo $r1['month']; ?></b></td>
            <td><b><?php echo $r1['year']; ?></b></td>
            <td><b><?php echo $r1['madePerMonth']; ?></b></td>
        </tr>
        <?php } ?>
    </tbody>
</table>

This error shows: 1.An alias was previously found. (near "month" at position 56) can anyone see the issue? any help is appreciated.

Possible duplicate of [MySQL Query GROUP BY day / month / year](https://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year) — , Jul 31 '17 at 21:35
Change alias `MONTH(pay_date) as month` (month) and `YEAR(pay_date) as year` (year) example change `MONTH(pay_date) as month_alias` — Guillermo Andres Fuentes Moral, Jul 31 '17 at 21:35
Thank you guys, it was a combination of using the reserved words and the missing comma. — colinreedy674, Aug 01 '17 at 22:05

score 1 · Answer 1 · answered Jul 31 '17 at 21:38

1

Month or month is a reserved word in MySQL (MySQL Documentation). This could be what is causing your errors. You should use the solution from the comment by Guillermo Andres Fuentes Moral.

answered Jul 31 '17 at 21:38

Wouter Pol

1,031
1
8
16

Query to group month,year, amount

1 Answers1