CODEWITHSUNDEEP
sqlpostgresqlgroup-bysubquery
SPAAAAACE
SPAAAAACE

Reputation: 1

How do I make values not repeat in SQL GROUP BY when I add the date?

I started with SQL recently and I'm a bit confused. I want to apply this query, but add a date column:

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
            SUM(p.amount) Pay_amount
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

But whenever I add the date column, the result changes completely to repeating customers with different dates.

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
            SUM(p.amount) Pay_amount, 
            payment_date
FROM customer c
JOIN payment p ON c.customer_id = p.customer_id
GROUP BY 1, 3
ORDER BY 2 DESC
LIMIT 10;

EDIT: Here is the sample database I'm using: https://www.postgresqltutorial.com/postgresql-sample-database/

Upvotes: 0

Views: 47

Answers (2)

SPAAAAACE
SPAAAAACE

Reputation: 1

I found the solution. Thanks, everyone. I realize that my question was stupid.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use an aggregation function:

SELECT CONCAT(first_name, ' ', last_name) AS Full_name,
       SUM(p.amount) AS Pay_amount, 
       MAX(p.payment_date)
FROM customer c JOIN
     payment p
     ON c.customer_id = p.customer_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Upvotes: 1

Related Questions