CODEWITHSUNDEEP
sqlstringgroup-by
Don P
Don P

Reputation: 63718

SQL: GROUP BY with string data

What aggregate functions work on a string, when using a GROUP BY?

I would usually GROUP BY user_id, but I am not sure what aggregate function can work over a string.

Goal is to get the currency for each user_id as one row:

user_id   currency

12341235  "USD"
12341235  "USD"
12341235  "USD"
12341235  "USD"
05945949  "EURO"
05945949  "EURO"
05945949  "EURO"
99910277  "USD"
99910277  "USD"
99910277  "USD"

Finished data:

user_id   currency

12341235  "USD"
05945949  "EURO"
99910277  "USD"

Upvotes: 0

Views: 2831

Answers (4)

Satinder singh
Satinder singh

Reputation: 10198

Demo http://sqlfiddle.com/#!3/60ea7/5

A DISTINCT and GROUP BY usually generate the same query plan, so performance should be the same across both query constructs. GROUP BY should be used to apply aggregate operators to each group. If all you need is to remove duplicates then use DISTINCT. If you are using sub-queries execution plan for that query varies so in that case you need to check the execution plan before making decision of which is faster.

Upvotes: 2

Madhivanan
Madhivanan

Reputation: 13700

Group bny all columns make it Distinct

select user_id,currency from your_table
Group by user_id,currency

Upvotes: 1

manurajhada
manurajhada

Reputation: 5380

Actually, you don't need group by it can be done by distinct.

select distinct user_id,currency from your_table;

As you have same currency for one user at each row, here is no need of any aggregate function.

Upvotes: 2

akurniaga
akurniaga

Reputation: 21

Is there a particular reason why you want to use group by?

To get the same result you should be able to use DISTINCT, e.g.

Select Distinct user_id, currency

Upvotes: 1

Related Questions