CODEWITHSUNDEEP
sqlgoogle-bigquery
Scylla
Scylla

Reputation: 33

Remove duplicate data based on date in SQL

I want to remove duplicate data based on dates. Basically a customer can log in on a website multiple times a day but I want to see one login per customer per day.

Columns: loginId (always different), loginTimeStamp, customerName.

I tried:

SELECT *
FROM table1
JOIN table2
ON table1.a = table2.a
GROUP BY CAST(loginTimeStamp as DATE)

I get the error "Star expansion expression references column loginId which is neither grouped nor aggregated at". The error comes up with all the columns. When I load all data without trying to group it it looks fine.

Upvotes: 0

Views: 118

Answers (1)

LMigMa49
LMigMa49

Reputation: 111

If you need to get one row per day and customer, and your loginTimestamp column is in YYYY-MM-DD format, you can group by customerName (you can also get number of logins per day with count(loginId)):

SELECT customerName, CAST(loginTimeStamp as DATE), count(loginId) 
FROM table1 JOIN table2 ON table1.a = table2.a
GROUP BY customerName, loginTimestamp

Upvotes: 1

Related Questions