Grouping by MONTH in SQL

Question

I am trying to put together a table that will take a full date, YYYY-MM-DD, pull out only the month information, and then group the months together as individual rows.

I've tried using the MONTH(DATE) command, with a Group by and Order By command, and I get the months returned for the 4 years listed on the table. I gives back 48 rows, listed 1-12, repeating 4 times. I want to get 1 return of 1-12 in order.

Here is the code I have so far.

select MONTH(DATE) As "Month"
from DW******.SL****
Group By DATE
Order by DATE 

Just started using SQL, so I apologize for the simple question.

If someone could please give me a hand. This is Db for i and I was going to use CONVERT but that does not work with our server.

Solution 1

can't you use month(date) in your group by?

select MONTH(DATE) As "Month"
from DW******.SL****
Group By MONTH(DATE)
Order by MONTH(DATE)

Solution 2

You need group by MONTH(Date), not DATE

select MONTH(DATE) As "Month"
from DW******.SL****
Group By MONTH(DATE)
Order by MONTH(DATE) 

Solution 3

Another approach may help you in general as you get into more complicated queries which allows you to mentally break things down in chunks (or stages).

It can look something like this:

WITH  x as
( fullselect1)
fullselect2

Where x refers to the fullselect1, and x is used as a FROM table in fullselect2. You can think of this as step 1 and step 2, although the optimizer can accomplish this however it chooses.

In your case here you could say

with Q as
( select MONTH(DATE) as mo, units
    from DW******.SL**** 
)
select mo, sum( units ) qty_sold
  from Q
  group by mo
  order by mo

This concept, while not necessary here, scales very well, and can help you write complex queries by breaking it into parts that are easier to comprehend.

If you look at this in the reference manual (I'll provide a link into the information center) IBM refers to this as a Common Table Expression. [CTE ]

Solution 4

If you need Month in terms of January, February, March, etc, use below query

SELECT MONTHNAME (CURRENT_TIMESTAMP) from customers;

View more solutions

Comments

[William]

SQL is not case sensitive, so whether I enter (Date), (DATE), or (date) I get the same return.

[William]

Excellent! Thank you, I was overlooking the fact I needed MONTH(DATE) in the Order By as well.

[msmucker0527]

I think his point is to group by MONTH(DATE) instead of the full DATE, which is correct

[BellevueBob]

@William He's not talking about the case of the word.

[msmucker0527]

more specifically, the problem was that you were grouping by DATE instead of MONTH(DATE), the order by has nothing to do with the # of rows returned

[William]

Correct, i apologize. EricZ was right. I had tried MONTH(DATE) in the Group By statement once. I overlooked that I needed it in the Order by as well. Much appreciated.