CODEWITHSUNDEEP
sqlpostgresql
Anthony
Anthony

Reputation: 35938

How to do a group by on specific rows

I am running the following query to get data back:

SELECT DISTINCT student_id, student_name from class_students where city = 'foobar' and student_id <> 0;

The data I get back looks like this:

 student_id |              student_name
------------+----------------------------------------
  41990     | James
  48220     | John
  00000     |
  00000     | lkjkj
  00000     | random name
  00000     | somethingelse

I would rather like to get this data back:

 student_id |              student_name
------------+----------------------------------------
  41990     | James
  48220     | John
  00000     | Name-doesnt-exist

Which means grouping all 00000 into one row with name NA

Upvotes: 1

Views: 137

Answers (3)

shawnt00
shawnt00

Reputation: 17915

If more than one name exists then flag it.

select student_id,
    case when count(distinct student_name) > 1 then 'N/A' else min(student_name) end as student_name
from class_students
where city = 'foobar' and student_id <> 0
group by student_id

Also you could eliminate these groups from the results entirely:

having count(distinct student_name) = 1

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try using row_number()

select student_id,coalesce(student_name,'Name-doesnt-exist') as name
from
(
SELECT student_id, student_name,row_number() over(partition by student_id order by case when student_name is null then 1 else 2 end) as rn
from class_students where city = 'foobar' and student_id <> 0
)A where rn=1

Upvotes: 0

juergen d
juergen d

Reputation: 204766

SELECT student_id, case when student_id = '00000' then 'N-A' else max(student_name) end
from class_students 
where city = 'foobar' 
  and student_id <> 0
group by student_id

Upvotes: 1

Related Questions