Question 90365 in PostgreSQL Question posted in
The official documentation can be found here.

Spring Data, JPQL, Group by month of a date column(type LocalDate) – Postgresql

I have to get total price in month from a date yy-mm-dd with jpql query but I can’t do it.

@Query(value = "select new com.talan.food.dto.MonthIncomes( function('date_format',date,'%Y-%m'),SUM(p.price)) from Reservation p group by function('date_format',p.date,'%Y-%m')" )

public List<MonthIncomes> getIncomeByMonth();

And in the table of entity I have:

public class Reservation {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private double price;
    @ManyToOne @JoinColumn(name="userId" )
    public User  user;
    private LocalDate date;
    private boolean confirmed;
}

And I will put the result in the class :

public class MonthIncomes {
    private LocalDate date ;
    private double price;

    public MonthIncomes (LocalDate date, double price) {
           this. date=  date;
           this.price = price;
    }
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    i resolve the problem by using this syntaxe:

    @Query(value = "select new com.food.dto.MonthIncomes(EXTRACT(month from p.date),SUM(p.price)) from Reservation p group by EXTRACT(month from p.date) ORDER BY EXTRACT(month from p.date)")

public List<MonthIncomes> getIncomeByMonth();

  2. 0

    You could something like this in your query

    SELECT p.date as date , 
SUM(p.price) as price 
FROM reservation p 
WHERE p.date >= '2022-01-01'
GROUP BY
EXTRACT(month from p.date)

    depending on the amount of yours you have to go back you would have to change the extract statement a bit. Nevertheless your problem looks like something that you could approach with the extract function

    https://www.postgresqltutorial.com/postgresql-date-functions/postgresql-extract/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search