0

I have a table with 2 fields: where someid2 is unique filed

GID  Name     Some_ID1    Some_ID_2 (unique)     
--  -------  -------    -------
1   A         A1           K1
1   A         A1           K2   
1   A         A1           K3
1   A         A2           K4
2   B         B1           K4
3   C         C1           K5
3   C         C2           K6
3   C         C3           K7
3   C         C4           K8

I want to group them by name and Gid, with 'count' of two columns

  GID  Name     Count(Some_ID1)    Count(Some_ID_2)      
    --  -------  -------           -------
    1   A         2                4
    2   B         1                1
    3   C         4                4

what i hve tried is grouped the column gid and name but i cant figure out to get count of some_id1

SELECT
gid, name
count( Some_id1) as Some_id1_count , -- need to fix count here
count( Some_id2) as Some_id2_count 
FROM table_a        
GROUP BY gid, name
BUlle7
  • 153
  • 3
  • 15
  • `count(distinct Some_id1)` and fix your sample data. I guess in the last row `K7` is wrong. – forpas Oct 01 '20 at 10:20

1 Answers1

3

I think you just want count(distinct):

select gid, name, count(distinct some_id1) cnt1, count(*) cnt2
from table_a
group by gid, name
GMB
  • 216,147
  • 25
  • 84
  • 135
  • thanks that helped, i was doing partition,cte and some other query but the answer was simple :D . – BUlle7 Oct 01 '20 at 10:26