CODEWITHSUNDEEP
sql
user1899415
user1899415

Reputation: 3125

SQL: Select distinct one column but include all fields

How can I select distinct one column (user) and then output the rest of the fields based on this one column?

Input:

  user     age      country
--------------------------------
  Tom      34        US
  Tom      32        EN
  Dick     29        MX
  Dick     29        DE
  Harry    15        CA

output (distinct user column, and pick one row to output for rest of fields):

  user     age      country    count
--------------------------------------
  Tom      34        US         2
  Dick     29        MX         2
  Harry    15        CA         1

Any help would be appreciated!

Upvotes: 0

Views: 708

Answers (2)

Daniel Sh.
Daniel Sh.

Reputation: 2074

SELECT USER, AGE, MAX(COUNTRY), COUNT(*) 
FROM TABLE
GROUP BY USER, AGE

You could try changing the MAX for a MIN. No need for a DISTINCT here. You could use some data format like SUBSTRING, but I'm not sure if the rest of the data will always be like that US and USS etc. Buy if you have more than 2/3 or if the changes start beyond a specific character you may encounter some wrong query results.

According to comments and updates.

SELECT USER, MAX(AGE), MAX(COUNTRY), COUNT(*)
FROM TABLE
GROUP BY USER.

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

SELECT user, age, country, COUNT(*) AS c_rec FROM
(
SELECT DISTINCT user, age, SUBSTRING(country, 1, 2) AS country FROM yourTable
) T
GROUP BY user, age, country

Upvotes: 0

Related Questions