• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

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
0
newbie1977
Asked:
newbie1977
  • 3
  • 3
1 Solution
 
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
sdstuberCommented:
as the data volume grows,  selecting from your table only once vs a self join should scale better.

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now