Returning the total with the date in a query with the SUM

Asked

Viewed 40 times

0

I need to return a sum of the value field of the table m and together with it the date of the initial field of the field sale_date, and showing Aggregate Function error:

SELECT 
    sum(m.value),
    t.sale_date 
from t_transaction t 
left join t_movement m on m.transaction_id =  t.id 
where t.paybox_id = 26

You would need to return the total accompanied by the date. The date is the same for all records...

Note: without the t.sale_date returns the total of value...

java sql postgresql

2 answers

2


Fields that are not in the aggregation functions, should be informed in the Group By, in his case the t.sale_date. Without this field, it works because there are none other than what is in the function sum().

SELECT 
    sum(m.value), 
    t.sale_date 
from t_transaction t 
left join t_movement m on m.transaction_id = t.id 
where t.paybox_id = 26
group by t.sale_date

Documentation of Select syntax: https://www.postgresql.org/docs/9.0/static/sql-select.html

  • So I’ve done this process before, but I stop totaling the values... What I want from id 26: value / sale_date 364.54 / 2018-01-08 12:07:43.68

  • then sale_date is not the same for movements. Put your table structure and a sample of data you have in them please, plus an example of how you want to output the data @aleestevao

  • 1

    Rovann, I solved the problem by creating two queries. Thank you!

0

I think I understand what you want:

SELECT 
    sum(m.value), 
    MIN(t.sale_date) 
from t_transaction t 
left join t_movement m on m.transaction_id = t.id 
where t.paybox_id = 26

That’s right ?

  • I solved the question using two queries. Thank you!

Browser other questions tagged java sql postgresql

You are not signed in. Login or sign up in order to post.