How to get the data month by month?

• ahmad.rock51 14335

  Newbie

  Points: 3

  More actions

  February 26, 2012 at 3:55 am

  #253687

  Hi all,

  I have table "Order", i want to get the count of order data month by month

  For Example,

  Order Receive JAN FEB MARCH APRIL MAY JUNE JULY AUG SEP OCT NOV DEC

  840 1 4 5 10 20 40 40 50 100 120 220 230

  can you suggest me efficient query for this

• Dave Ballantyne

  SSC-Dedicated

  Points: 33667

  More actions

  February 26, 2012 at 4:17 am

  #1451809

  Hi Ahmad,

  Investigate the COUNT aggregate function first. When comfortable with that , take a look at the PIVOT operator.

  Personally i prefer to make the PIVOT'ing a frontend/disply issue though.

  ---

  
  Clear Sky SQL
  My Blog[/url]

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 26, 2012 at 7:24 am

  #1451824

  First lets get some data into a table

  CREATE TABLE #Order(Quantity INT,Placed DATETIME)

  INSERT INTO #Order

  SELECT 100,'1/20/2011' UNION ALL

  SELECT 1,'2/7/2011' UNION ALL

  SELECT 2, '3/1/2011' UNION ALL

  SELECT 2, '2/5/2011' UNION ALL

  SELECT 3, '4/18/2011' UNION ALL

  SELECT 9, '5/6/2011'

  -- Now answer the OPs question

  SELECT *

  FROM (

  SELECT Quantity,MONTH(Placed) [Month] FROM #Order

  ) TableDate

  PIVOT (

  SUM(Quantity)

  FOR [Month] IN (

  [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

  )

  ) PivotTable

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Jeff Moden

  SSC Guru

  Points: 1004447

  More actions

  February 26, 2012 at 2:06 pm

  #1451844

  I prefer not using the PIVOT operator for many reasons including some that are performance related. Please see the following article for more information.

  http://www.sqlservercentral.com/articles/T-SQL/63681/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)