Why datediff doesn't work?

[rambo19997]

I’m using libreoffice base.
I want to group by week from specified date and sum Prezzo.

I want for example:

1_________________9.87

2_________________63.5

How I can do?

edit: and to numerate the results of the entire query, How I can do?

Thanks

[Ratslinger]

It appears yet another modification to the question:

and to numerate the results of the entire query

A Query is a result set based upon a condition or total lack of condition. You cannot have a total of everything and a total of only particulars within the same query.

[rambo19997]

I want only to add a row_number next to each result.

[Ratslinger]

Sorry, misread.

[rambo19997]

Do you know why ROW_NUMBER() doesn’t work?

[Ratslinger]

Hello,

Your main question is not what the detail explains. In the details see nothing concerning datediff. Also you cannot select * as you are producing totals and fields such as ID are not relevant.

To total simply by week:

SELECT SUM("Prezzo") From "Vendite" Group by WEEK("Giorno")

For totaling week within each year:

SELECT SUM("Prezzo") From "Vendite" Group by WEEK("Giorno"), YEAR("Giorno")

Edit based upon additional request:

To add the start of a specific date, use a Where condition (date comparison is in format YYY-MM-DD):

SELECT SUM("Prezzo") As "Total", WEEK("Giorno") From "Vendite" Where "Giorno" > '2021-03-01' Group by WEEK("Giorno")

will group only dates after March 1 of 2021

Edit #2:

Please understand that this is a question and answer site. Ask a question and get an answer. Further questions should be asked as new questions. As for the latest question:

Do you know why ROW_NUMBER() doesn’t work?

Because it does not exist in HSQLDB embedded. That is v1.8 and that function was not introduced until v2.x series.

As for a numerator, this is not a normal request. Possibly use a sequence generator. See answer in this post:

Synchronise scrolling of subforms

and link in this answer:

Add AutoValue field

[rambo19997]

Sorry Ratslinger for the wrong question.
I’m using Windows 10, HSQLDB incorpored and LibreOffice 7.0.3.1.

I want a query like this but with the visualization of number of week starting from a specified date and not from the beginning of the year.

SELECT SUM("Prezzo") as TOTAL, WEEK("Giorno") From "Vendite" Group by WEEK("Giorno")

I would like a row number for each result of the select

[Ratslinger]

Please do not use answers for comments or additional information. You can edit your original question.