25099

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 <ul><li>Since I have no sales no march the result must return march sales with quantity 0.</li> <li>

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(); </li> </ul>

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

Answer1:

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 <strong>months</strong>

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 <a href="http://sqlfiddle.com/#!2/02808/5" rel="nofollow">SQL Fiddle</a> that would help you

Recommend

  • How can I omit a column from SELECT statement while using it into HAVING clause?
  • How can I use aliases on GROUP BY clause?
  • Use vars from before WITH statement in RETURN statement in Neo4j Cypher
  • WebMethod returning generic list
  • Generating and downloading an excel file generates a ERR_INVALID_RESPONSE
  • Extending Boostrap to include extra small inline form
  • Sending mail with ASP.NET
  • Python Beginner: How can I make text #1 look like text #2
  • magento success page variables
  • Subquery returns more than 1 row
  • How to get list of users who's birthday is today in MongoDB
  • How to use arithmetic operators with SAS macro variables [duplicate]
  • how to check the textarea content is blank using javascript?
  • LINQ join with filter criteria
  • SQL getting multiple columns into one column
  • HighCharts - Show tooltip on column where value is 0 or null
  • Find 5 consecutive numbers in numpy array by row, ignore duplicates
  • In C what exactly happens if i use () to initialize a double dimension array instead of the {}?
  • Python pandas melting data to multiple columns and coulmn names in another column
  • Is it possible to define rest argument in OCaml?
  • Find Previous month name using Calender or any classes that in java
  • Invalid Date on validation Date of js
  • MySQL Order by column = x, column asc?
  • java inputstream
  • Breeze - Deleted Items nav properties bug
  • Read text file and split every line in MSBuild
  • javaw.exe and eclipse startup problems
  • TFS: Get latest causes slow project reloading
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Updated Ionic CLI but shows previous version (Windows)
  • Numpy divide by zero. Why?
  • php design question - will a Helper help here?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • AngularJs get employee from factory
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • embed rChart in Markdown
  • need help with bizarre java.net.HttpURLConnection behavior
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Authorize attributes not working in MVC 4
  • Converting MP3 duration time