Sql in Postgresql: Do Not Repeat Values from a Table field

Asked

Viewed 353 times

-1

I have a select that displays the names, date and amounts paid by customers. I must display a list without repeating the names.

SELECT  
    c.name,
    p.created_at,
    p.amount_paid
FROM 
    payments as p, 
    clients as c
WHERE 
  p.created_at < CURRENT_DATE AND p.created_at > CURRENT_DATE -199

post

php sql laravel postgresql select

  • if you will not repeat the names, you will normally have to group date and value. You can use the first or last date, average or sum of values. What you need ?

  • the idea is to add up the payments of each client

  • as the form payments made the previous day, do not need to group by date

  • but if it has multiple dates, you need to put in an aggregation function, or group by date

  • no need. date field is unique: previous day!

  • the goal is not to repeat the name of the customer and sum all payments made by him on the previous day

  • in this case, the date enters the cluster. see the answer

  • What is the table for receipt_status ? Like the table clients is relating to others ? And the purpose of the table receipts ?? You want the sum of amount_paid grouped by the date of creation created_at ?

  • edited the code. But already published the answer to my problem!

  • How about posting the original structure of the tables ?

  • I am in doubt about selecting all tables fields!!

  • how can I select other table fields ??

Show 7 more comments

1 answer

1


The tables receipt_status and receipts are not necessary in that query, they do not relate to any other table and none of their fields are recovered!

I couldn’t see how to calculate the "paid amounts paid by customers" if the table client does not relate in any way to the other tables!

I suggest you review the logic of your query, although it works, it is certainly not recovering the information correctly.

The proposed solution would be something like:

SELECT  
    c.name,
    p.created_at,
    SUM(p.amount_paid) AS total_amount_paid
FROM 
    payments AS p 
JOIN
    payment_status AS ps ON ( ps.id = p.payment_status_id ) 
CROSS JOIN
    clients AS c
WHERE 
    p.created_at BETWEEN current_date - '199 days'::interval AND current_date
GROUP BY
    c.name,
    p.created_at;

Browser other questions tagged php sql laravel postgresql select

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