group by monthname in sql

Question

I have a table which looks like:

id    |   Item  | Quantity | Amount | created
________________________________________________
1     | Monitor | 10       | 5000   | 2013-01-11
2     | Keyboard| 10       | 200    | 2013-02-19
3     | Monitor | 10       | 5000   | 2013-02-13
3     | Keybard | 10       | 200    | 2013-04-19

when I pass the query:

SELECT monthname( created ) AS
MONTH , sum( quantity ) AS qty
FROM `sales`
WHERE year( created ) = '2013'
GROUP BY monthname( created )
ORDER BY monthname( created ) DESC

It gives the result:

month      |  qty    
______________________
January    | 10
February   | 20
April      | 10

But what I was trying to retrieve is:

month      |  qty    
______________________
January    | 10
February   | 20
March      | 0
April      | 10

Since I have no sales no march the result must return march sales with quantity 0.

I am using Codeigniter in my application so if we can't solve it through sql then may be you can show me the way to solve it through Codeigniter.

$this->db->select('monthname(created) as month, sum(quantity) as qty');
$this->db->from('sales');
$this->db->where('year(created) = 2013');
$this->db->group_by('monthname(created)');
$this->db->order_by('monthname(created)');
$this->data['sales'] = $this->db->get()-result();

In the view:

$data = array();

foreach($sales as $sale) {
  $data[] = $sale->qty;
}

Output of $data:

10, 20, 10 in array;

what I need is

10, 20, 0, 10 array

check here http://stackoverflow.com/questions/17916322/mysql-to-select-month-wise-record-even-if-data-not-exist — Abhik Chakraborty, Jan 11 '14 at 08:52
or here http://stackoverflow.com/questions/11516688/sql-to-select-one-record-for-every-month-with-a-sum-of-that-months-records — Roman Pickl, Jan 11 '14 at 08:53

Guns · Answer 1 · 2014-01-11T09:13:47.317

I think the best option is to have a month table which looks as follows

id | Month
---------------------
 1 | January
 2 | February
 3 | March
 4 | April
 5 | May
 6 | June
 7 | July
 8 | August
 9 | September
10 | October
11 | November
12 | December

name this table as months

and you can use the query as below

SELECT
    months.`month`,
    Sum(sales.quantity)
FROM
    sales
RIGHT OUTER JOIN months ON months.`month` = monthname(sales.created)
GROUP BY
    months.`month`
ORDER BY
    months.id

This should work fine!

Here is the SQL Fiddle that would help you

group by monthname in sql

1 Answers1

Linked