How to group only by month and year?

Question

I'm rather vaguely familiar with SQL. I use SQL Server 2012.

I have this table:

|Id  | SiteId|    SiteDesc |IsNormal|     DateReview           |FrequencyId|
|3379|      5|     colon   |    1   |   2016-09-10 00:00:00.000|    1      |    
|3381|      5|     colon   |    0   |   2016-09-15 00:00:00.000|    1      |    
|3382|      5|     colon   |    1   |   2016-09-21 00:00:00.000|    1      |

|3489|      5|     colon   |    0   |   2016-08-10 00:00:00.000|    1      |    
|3851|      5|     colon   |    1   |   2016-08-16 00:00:00.000|    1      |

|3537|      2|     dogon   |    1  |    2016-05-05 00:00:00.000|    1      |    
|3863|      2|     dogon   |    1  |    2016-05-20 00:00:00.000|    1      |

IsNormal column is of BIT data type.

I need to group the table by SiteId and DateReview (only month and year). If in IsNormal column at least one row has property false, in grouped table it has to be False.

Here is the desired grouped table:

 | SiteId|    SiteDesc |IsNormal|     DateReview           |FrequencyId|
 |      5|     colon   |    0   |   2016-09-10 00:00:00.000|    1      |        
 |      5|     colon   |    0   |   2016-08-10 00:00:00.000|    1      |    
 |      2|     dogon   |    1   |   2016-05-05 00:00:00.000|    1      |

@gaurav any item in the select with a group by has to be either in the group by or an aggregate: that does not meet this rule. — Richard, Oct 20 '16 at 09:08

score 6 · Accepted Answer · answered Oct 20 '16 at 09:42

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

Just curious why not `GROUP BY YEAR(DateReview), MONTH(DateReview)`? — Eric, Oct 20 '16 at 10:14
@Eric, In this case there is no difference. The formula with `DATEDIFF` is more generic. You can put `week`/`quarter`/`hour`/etc instead of `month` to group by some other interval. — Vladimir Baranov, Oct 20 '16 at 10:25

score 3 · Answer 2 · answered Oct 20 '16 at 10:45

Use year() and month():

select SiteId, min(SiteDesc) as SiteDesc,
       min(case when IsNormal = 0 then 0 else 1 end) as IsNormal,
       min(DateReview) as DateReview,
       min(FrequencyId) as FrequencyId
from t
group by SiteId, year(DateReview), month(DateReview)
order by min(DateReview) desc;

This simply uses the year and month for the aggregation. It then pulls the column values using an aggregation function. For DateReview, the appropriate function appears to be min().

score 0 · Answer 3 · answered Apr 21 '18 at 12:02

I have used this code you can try also.

 select  CAST(MONTH(SalesDate) AS VARCHAR(2)) + '-' + CAST(YEAR(SalesDate) AS VARCHAR(4)),  sum(TSalesAmt) as 'GrandTotal' from CreditSales group by  CAST(MONTH(SalesDate) AS VARCHAR(2)) + '-' + CAST(YEAR(SalesDate) AS VARCHAR(4))

How to group only by month and year?

3 Answers3