Group by month name in sql using SqlKata

Question

I have the following query, and I want to group the result by the month instead of the full date. Is there any easy way to achieve that ? noting that I am using the SqlKata library

Expected Result

Month | Balance
------|--------
1     | 100
2     | 150
3     | 300

Instead of

Date       | Balance
--------   |------
2018-01-01 | 100
2018-01-02 | 90
.....      | ....
2018-02-01 | 150

My current code is:

var balances = db.Query("Balances")
    .WhereBetween("Date", from, to)
    .Select("Date")
    .Select("sum([Balance]) as Balance")
    .GroupBy("Date")
    .Get();

Answer 1

You have GroupByRaw ( and SelectRaw ) Just use a database built in functions to get the month. Let's supose you are working with SQL Server and MONTH function:

var balances = db.Query("Balances")
    .WhereBetween("Date", from, to)
    .SelectRaw("MONTH(Date)")
    .Select("sum([Balance]) as Balance")
    .GroupByRaw("MONTH(Date)")
    .Get();

Disclaimer: check your database brand docs for date functions to know how to get month from date in your case.