YEAR an MONTH function for sum per month.

[Christian Master]

Is there a YEAR function in JOOQ?

I need to sum total per month. In SQL i write:

SELECT YEAR(myDate) as SomeYear,
         MONTH(myDate) as SomeMonth,
         SUM(someInt) AS SomeTotal
    FROM myTable
GROUP BY YEAR(myDate), MONTH(myDate)
ORDER BY YEAR(myDate), MONTH(myDate)

Is it possible in JOOQ?

Thx

Chris

[Samir Faci]

I think what you're looking for is:

DSL.year(), DSL.month()  and DSL.sum() respectively.

--

Samir Faci

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Christian Master]

Thx Samir, this works.

Connection conn = DriverManager.getConnection("jdb........

Result<Record3<Integer, Integer, BigDecimal>> fetch1

                    = DSL.using(conn)

                            .select(DSL.year(TABLE.DATE),

                                    DSL.month(TABLE.DATE),

                                    DSL.sum(TABLE.MYBIGINT))

                            .from(TABLE)

                            .groupBy(DSL.year(TABLE.DATE), DSL.month(TABLE.DATE))

                            .orderBy(DSL.year(TABLE.DATE), DSL.month(TABLE.DATE))

                            .fetch();

[Samir Faci]

No worries.

Some of those methods are available off the field name as well.  I'm not sure about the year/month but you could probably do something like

TABLE.MYBIGINT.sum()  

that's just a preference on your end on what you find more readable, the end result I believe is about the same.

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

2016-12-24 0:27 GMT+01:00 Samir Faci <sa...@esamir.com>:

Some of those methods are available off the field name as well.  I'm not sure about the year/month but you could probably do something like

year/month aren't available from the Field reference. Having this OO-style, post-fix notation was an early design mistake, which is not deprecated in the API yet, because it is very widely adopted and it was in there from very early days.

One big drawback of the "post-fix" notation is that Java doesn't support something similar to "partial" methods, i.e. a method that is only available on e.g. Field<? extends java.util.Date | java.time.Temporal> but not on Field<? extends Number>, so there's no type safety at all anymore for "post-fix" functions. They just "work" (compile) on any Field<?>.

"Prefix" functions (those in DSL) are generic methods that can introduce a new generic type constraint, so these type checks are possible again. Well, if only Java supported union types in generics, not just intersection types :-/

Eventually, we'll generally prefer the SQL style of applying actual functions to expressions, not "methods" to "objects".

Hope this helps,
Lukas