search

sqlkata / querybuilder

SQL query builder, written in c#, helps you build complex queries easily, supports SqlServer, MySql, PostgreSql, Oracle, Sqlite and Firebird
https://sqlkata.com
MIT License
3.08k stars 498 forks source link

Iss 507 support filter clause #617

Closed ahmad-moussawi closed 1 year ago

ahmad-moussawi commented 1 year ago

This PR adds the support for the SelectAggregate method and it's siblings SelectSum, SelectMax, SelectMin, SelectAvg, SelectCount methods, this is better than using SelectRaw.

All these methods accepts an optional filter parameter to filter the aggregated column based on a specified conditions, this will translate to the Aggregation Filter clause on supported compilers like PostgreSQL, and SQLite with a fallback to CASE WHEN statement for the unsupported ones.

Related #507

yankyhgoflow commented 1 year ago

👍 Thanks, this is really taking this library to the next level!

Thanks @ahmad-moussawi you rock!

kenzouno1 commented 1 year ago

Missing alias for this feature

ahmad-moussawi commented 1 year ago

@kenzouno1 what do you mean exactly by missing alias? is this what you are looking for?

var query = db.Query("Transaction")
                .SelectSum("Amount as Total_2020", q => q.WhereDatePart("year", "date", 2020))
                .SelectSum("Amount as Total_2021", q => q.WhereDatePart("year", "date", 2021))
                .SelectSum("Amount as Total_2022", q => q.WhereDatePart("year", "date", 2022))

check more examples here https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder.Tests/MySqlExecutionTest.cs#L237

kenzouno1 commented 1 year ago

That's exactly what I'm looking for. Thank you