SQL Grouping by Month and Year

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 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')

How to group by month and year?

If you have the sample data:

CREATE TABLE purchase ( purchasedate ) AS
SELECT DATE '2019-01-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT DATE '2019-02-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT DATE '2020-01-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT DATE '2020-02-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT DATE '2020-03-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT DATE '2020-04-01' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 1;

Then, you can use your query:

SELECT TO_CHAR(PURCHASEDATE, 'MM YYYY') AS monthyear,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       TO_CHAR(PURCHASEDATE, 'MM YYYY');

Which outputs:

MONTHYEAR | FREQUENCY
:-------- | --------:
03 2020   |         4
01 2019   |         5
01 2020   |         3
02 2020   |         2
02 2019   |         3
04 2020   |         1

Or, you can use TRUNC:

SELECT TRUNC(PURCHASEDATE,'MM') AS monthyear,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       TRUNC(PURCHASEDATE,'MM');

Which outputs:

MONTHYEAR           | FREQUENCY
:------------------ | --------:
2020-03-01 00:00:00 |         4
2020-04-01 00:00:00 |         1
2020-02-01 00:00:00 |         2
2020-01-01 00:00:00 |         3
2019-01-01 00:00:00 |         5
2019-02-01 00:00:00 |         3

Or, you can use EXTRACT:

SELECT EXTRACT( YEAR FROM PURCHASEDATE) AS year,
       EXTRACT( MONTH FROM PURCHASEDATE) AS month,
       COUNT(*) AS frequency
FROM   PURCHASE
GROUP BY
       EXTRACT( YEAR FROM PURCHASEDATE),
       EXTRACT( MONTH FROM PURCHASEDATE);

Which outputs:

YEAR | MONTH | FREQUENCY
---: | ----: | --------:
2019 |     1 |         5
2020 |     1 |         3
2020 |     2 |         2
2020 |     4 |         1
2019 |     2 |         3
2020 |     3 |         4

db<>fiddle here

Group by month & year

You can use date_format():

SELECT 
    date_format(CAST(t1.some_date as DATE), '%m %Y') as Issue_Month,
    COUNT(*) as c
FROM
    (sub query) t1
GROUP BY 
    date_format(CAST(t1.some_date as DATE), '%m %Y')

Group by month and year in MySQL

GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);

is what you want.

Count and group data by Year/month

Based on your new input, I created a table variable with all the year/month between the first and last ticket, I used this article for that:
better way to generate months/year table
Then I update each category, counting the tickets that have a closed date > first day of each month. This should give you the desired result.

Updated 8/17/2020 - Modified query to include tickets that were closed before the end of the month.

declare @FromDate datetime, 
        @ToDate datetime;

SET @FromDate = (Select min(created) From [dbo].[taskDB]);
SET @ToDate = (Select max(created) From [dbo].[taskDB]);

declare @openTicketsByMonth table (firstDayOfMonth datetime, firstDayNextMonth datetime, year int, month int, Low int, Medium int, High int, Critical int, NA int)

Insert into @openTicketsByMonth(firstDayOfMonth, firstDayNextMonth, year, month)

Select top  (datediff(month, @FromDate, @ToDate) + 1) 
                                                  dateadd(month, number, @FromDate),
              dateadd(month, number + 1, @FromDate),
             year(dateadd(month, number, @FromDate)),
              month(dateadd(month, number, @FromDate))
              from [master].dbo.spt_values 
              where [type] = N'P' order by number;

update R
Set R.Low = (Select count(1) from [dbo].[taskDB] where rating = 'Low' and created < R.firstDayNextMonth and (closed >= R.firstDayOfMonth or closed = '' or closed is null)),
    R.Medium = (Select count(1) from [dbo].[taskDB] where rating = 'Medium' and created < R.firstDayNextMonth and (closed >= R.firstDayOfMonth or closed = '' or closed is null)),
    R.High = (Select count(1) from [dbo].[taskDB] where rating = 'High' and created < R.firstDayNextMonth and (closed >= R.firstDayOfMonth or closed = '' or closed is null)),
    R.Critical = (Select count(1) from [dbo].[taskDB] where rating = 'Critical' and created < R.firstDayNextMonth and (closed >= R.firstDayOfMonth or closed = '' or closed is null)),
    R.NA = (Select count(1) from [dbo].[taskDB] where rating = 'N/A' and created < R.firstDayNextMonth and (closed >= R.firstDayOfMonth or closed = '' or closed is null))
From @openTicketsByMonth R

select  year,
        month,
        Low,
        Medium,
        High,
        Critical,
        NA 
from @openTicketsByMonth

How to GROUP BY Month-Year using BigQuery

you can use Standard SQL:

SELECT 
  FORMAT_DATE('%b-%Y', created_date) mon_year, 
  COUNT(1) AS `count`
FROM `project.dataset.table`
GROUP BY mon_year
ORDER BY PARSE_DATE('%b-%Y', mon_year)  

if you are using timestamp you have to cast it to date

SELECT
  FORMAT_DATE('%b-%Y', DATE(CURRENT_TIMESTAMP())) mon_year

will produce:

Sep-2020

As per your example from comments. You can't use count in where clause. If you want to have a filter on aggregation you have to use having docs.

SELECT TIMESTAMP_TRUNC(start_date, MONTH) AS year_month,
       start_station_name,
       end_station_name,
       count(start_station_name) AS count_start,
FROM bigquery-PUBLIC-data.san_francisco.bikeshare_trips
WHERE start_station_name <> end_station_name
GROUP BY year_month,
         start_station_name,
         end_station_name
HAVING count(start_station_name) > 10
LIMIT 50

MySQL Query GROUP BY day / month / year

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.

Group by month and year given yyyy-mm-dd as a date

SELECT innerSelect.dateMonth
, innerSelect.dateYear

FROM (SELECT (MONTH(yourTable.Date)) AS dateMonth
           , YEAR(yourTable.Date) AS dateYear
        FROM yourTable) AS innerSelect

GROUP BY innerSelect.dateMonth, innerSelect.dateYear

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

---

Related Topics