Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Grouping by MONTH in SQL

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.

like image 663
William Avatar asked Jan 11 '13 21:01

William


People also ask

Can we group by month in SQL?

Group By Month and Year We also use YEAR() and MONTH() functions to ensure that data is grouped and ordered by month and year numbers. In the above query, if we use date_format function in group by clause, then MySQL will sort the groups alphabetically, instead of chronologically.

How do I select data by month in SQL?

To select all entries from a particular month in MySQL, use the monthname() or month() function.

How do I Group A timestamp by month?

You can group month and year with the help of function DATE_FORMAT() in MySQL. The GROUP BY clause is also used.

Is there a month data type in SQL?

SQL Server MONTH() function overviewThe MONTH() function returns an integer value which represents the month of a specified date. The MONTH() function takes an argument which can be a literal date value or an expression that can resolve to a TIME , DATE , SMALLDATETIME , DATETIME , DATETIME2 , or DATETIMEOFFSET value.


Video Answer


2 Answers

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)
like image 87
Farzad Avatar answered Sep 18 '22 18:09

Farzad


You need group by MONTH(Date), not DATE

select MONTH(DATE) As "Month"
from DW******.SL****
Group By MONTH(DATE)
Order by MONTH(DATE) 
like image 26
EricZ Avatar answered Sep 21 '22 18:09

EricZ