Solved

Grouping two columns together

Posted on 2011-09-02
7
546 Views
Last Modified: 2013-12-19
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
Comment
Question by:newbie1977
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:cklautau
ID: 36472813
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
 
LVL 14

Expert Comment

by:leoahmad
ID: 36472843
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
 
LVL 14

Expert Comment

by:leoahmad
ID: 36472852
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36472904
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36472968
as the data volume grows,  selecting from your table only once vs a self join should scale better.

0
 
LVL 14

Expert Comment

by:leoahmad
ID: 36473034
@sdstuber

Nice approach
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36473067
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question