How to Group by Month from Date Field Using SQL

How to group by month from Date field using sql

I would use this:

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;

This will group by the first of every month, so

`DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)` 

will give '20130101'. I generally prefer this method as it keeps dates as dates.

Alternatively you could use something like this:

SELECT  Closing_Year = DATEPART(YEAR, Closing_Date),
        Closing_Month = DATEPART(MONTH, Closing_Date),
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category;

It really depends what your desired output is. (Closing Year is not necessary in your example, but if the date range crosses a year boundary it may be).

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

How to query GROUP BY Month in a Year

I would be inclined to include the year in the output. One way:

select to_char(DATE_CREATED, 'YYYY-MM'), sum(Num_of_Pictures)
from pictures_table
group by to_char(DATE_CREATED, 'YYYY-MM')
order by 1

Another way (more standard SQL):

select extract(year from date_created) as yr, extract(month from date_created) as mon,
       sum(Num_of_Pictures)
from pictures_table
group by extract(year from date_created), extract(month from date_created)
order by yr, mon;

Remember the order by, since you presumably want these in order, and there is no guarantee about the order that rows are returned in after a group by.

SQL grouping by month and year

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada 
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY 
  CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2) 

How to group by month using SQL Server?

SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]

You could also try:

SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month

SQL Grouping by Month on dates fields

Here is a query:

SELECT   Employee.Name      
, Employee.ID       
, Sum(Sales.Money)
, Year(Sales.Date)           
, Month(Sales.Date)
FROM Database.Sales.Sales       
INNER JOIN Database.Employee.Employee        
ON Sales.ID=Employee.ID        
WHERE Sales.Date BETWEEN '2000-01-01' AND '2001-01-01'            
group by 
    Employee.Name      
    , Employee.ID
    , Year(Sales.Date)           
    , Month(Sales.Date)
ORDER BY Employee.Name,  Year(Sales.Date), Month(Sales.Date)

How can I group by date time column without taking time into consideration

Cast/Convert the values to a Date type for your group by.

GROUP BY CAST(myDateTime AS DATE)

SQL group dates by month

You can do something like this:

e.g. how many units are due to expire in 2012:

SELECT MONTH(cvu.ExpirationDate) AS Mnth, YEAR(cvu.ExpirationDate) AS Yr, 
    COUNT(*) AS DueToExpire
FROM clientvehicleunit cvu
WHERE cvu.ExpirationDate >= '20120101' AND cvu.ExpirationDate < '20130101'
GROUP BY MONTH(cvu.ExpirationDate), YEAR(cvu.ExpirationDate)

How to group entries by month (from a date field) when `only_full_group_by` is on?

Hmmm . . . The problem is the date in the ORDER BY. A simple workaround is:

SELECT SUM(`payment`) AS total, DATE_FORMAT(`date`, '%Y-%m') AS month
FROM `my_table`
GROUP BY month
ORDER BY month DESC;

I used month in the GROUP BY as well, because MySQL conveniently allows that syntax.

Or, if you prefer:

ORDER BY MIN(`date`)

The bare date is not available for the ORDER BY because it is not in the result set generated by the GROUP BY.

How to group only by month and year?

SQL Server can't aggregate bit columns as is, so cast it to int first, then use MIN and then cast it back to bit.

To group by month I use the following formula:

DATEADD(month, DATEDIFF(month, '20010101', DateReview), '20010101')

You can pick any anchor date instead of '20010101', it can be any first day of the month. The idea is simple. DATEDIFF(month,...) calculates the number of months between the anchor date and the value from the table. This is integer number - how many times the boundary of the month was crossed. Then this number is added back to the anchor date. Result of this expression is the value of DateReview truncated to the first day of the month.

Query

SELECT
    SiteId
    ,MIN(SiteDesc) AS SiteDesc
    ,CAST(MIN(CAST(IsNormal AS int)) AS bit) AS IsNormal
    ,MIN(DateReview) AS DateReview
    ,MIN(FrequencyId) AS FrequencyId
FROM T
GROUP BY
    SiteId
    ,DATEADD(month, DATEDIFF(month, '20010101', DateReview), '20010101')

---

Related Topics