Mixing `SUM` with other columns

[Sharparam]

It doesn't seem possible to have a SUM in a SELECT column list together with other columns (unless you hardcode it with SelectRaw), is this correct?

Consider this query:

SELECT GroupId, SUM(Age)
FROM People
GROUP BY GroupId

I can only find AsSum on the query object, but this makes it only select a SUM, and discards any other columns that have been selected with Select().

It also requires an alias on the SUM, but that's a minor thing.

Here's an example in MySQL.

[ceastwood]

@Sharparam

at present-- SqlKata Documentation, about half-way down the page look for the header 'Raw'.

var query = new Query("People")
                .Select("GroupId")
                .SelectRaw("SUM(`Age`)")
                .GroupBy("GroupId");

Produces

SELECT `GroupId`, SUM(`Age`) FROM `People` GROUP BY `GroupId`

**edit: missed the part where you asked if correct, it is 😄

[Sharparam]

Yeah, anything can be done with Raw functions of course. But this foregoes the reason you'd be using SqlKata in the first place rather than writing raw SQL queries: automatically adapting to the target SQL server and whatnot. Backticks won't work in MSSQL for example (by default anyway).

[DeafLight]

@Sharparam I agree with you that the main aggregate functions should be implemented inside SqlKata. But until we get that, and according to the documentation, at least the string you pass to SelectRaw will also be resolved by the engine and any part enclosed in brackets [] will be translated into implementation specific identifiers. So SelectRaw("SUM([Age])") should work in MySQL as well.

[ahmad-moussawi]

Really nice idea, we are working on a template parser that can help in many situations, like date time manipulation, string functions and many more, we can implement this once the parser will be ready.

[asherber]

I'm interested in this as well. The more I can do without SelectRaw, the happier I am.

Like the OP, I would love to do something like:

var q = new Query("foo")
    .Select("bar")
    .AsMax("baz")
    .GroupBy("bar");

// SELECT [bar], MAX([baz]) AS [max] FROM [foo] GROUP BY [bar]

[generik0]

Thanks @asherber

why does

var q = new Query("foo")
    .Select("bar")
    .AsMax("baz as bazmax")
    .GroupBy("bar");

not produce:
// SELECT [bar], MAX([baz]) AS [bazmax ] FROM [foo] GROUP BY [bar]

(using "As" doesn't workj either)

also, why does

var q = new Query("foo")
    .SelectRaw("bar")
    .AsMax("baz")
    .GroupBy("bar");

produce, and the select raw is ignored?:
// SELECT MAX([baz]) AS [min] FROM [foo] GROUP BY [bar]

[ahmad-moussawi]

Now you can use the SelectAggregate #617