Grouping two columns together

I have two columns of levels per emplid. I would like to group the levels together and get the count. I tried grouping them together by level1 but the issue is if there is a number is level2 that isn't in level1 then it doesnt get counted. I am sure there is a simple solution but I can't seem to figure it out. Below is my data set and the results I would like..Thanks


Emplid   level1    level2
1234        3        4
2345        2        2
3456        4        5
4567        1        1
5678        3        3
6789       2         3
7899        2        3
8999        1        2


I want the results to look like this

level    frst_level_cnt      snd_levl_cnt
1                 2                           1
2                 3                           2
3                 2                           3
4                 1                           1
5                 0                           1
newbie1977Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
as the data volume grows,  selecting from your table only once vs a self join should scale better.

0
 
cklautauCommented:
Try this:

select l1.level,l1.frst_level_cnt,l2.snd_level_cnt
from
(select level1 level, count(1) frst_level_cnt
from <table>
group by level1) l1
full outer join
(select level2 level, count(1) snd_level_cnt
from <table>
group by level2) l2
on (l1.level=l2.level)
order by level
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
select level2, nvl(cnt_level_1,0) cnt_level_1, nvl(cnt_level_2,0) cnt_level_2
from
(select level1,count(level1) cnt_level_1 from cnt group by level1) tb1,
(select level2,count(level2) cnt_level_2 from cnt group by level2) tb2
where tb1.level1(+) = tb2.level2
/
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Muhammad Ahmad ImranDatabase DeveloperCommented:
I just double checked in sql plus

DB1> select * from cnt;

    EMPLID     LEVEL1     LEVEL2
---------- ---------- ----------
      1234          3          4
      2345          2          2
      3456          4          5
      4567          1          1
      5678          3          3
      6789          2          3
      7899          2          3
      8999          1          2

8 rows selected.

DB1> select level2, nvl(cnt_level_1,0) cnt_level_1, nvl(cnt_level_2,0) cnt_level_2
  2  from
  3  (select level1,count(level1) cnt_level_1 from cnt group by level1) tb1,
  4  (select level2,count(level2) cnt_level_2 from cnt group by level2) tb2
  5  where tb1.level1(+) = tb2.level2
  6  /

    LEVEL2 CNT_LEVEL_1 CNT_LEVEL_2
---------- ----------- -----------
         1           2           1
         2           3           2
         3           2           3
         4           1           1
         5           0           1

0
 
sdstuberCommented:
ELECT   lvl,
         COUNT(DISTINCT DECODE(level1, lvl, emplid)) frst_level_cnt,
         COUNT(DISTINCT DECODE(level2, lvl, emplid)) snd_levl_cnt
    FROM (SELECT t.*, DECODE(n, 1, level1, level2) lvl, n
            FROM yourtable t,
                 (SELECT 1 n FROM DUAL
                  UNION ALL
                  SELECT 2 FROM DUAL))
GROUP BY lvl
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
@sdstuber

Nice approach
0
 
sdstuberCommented:
thanks, anytime I hit the same source multiple times I always have to ask myself if it's really necessary and usually my answer is no.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.