Solved

# Grouping two columns together

Posted on 2011-09-02
539 Views
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
Question by:newbie1977
• 3
• 3

LVL 3

Expert Comment

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

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

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

LVL 73

Expert Comment

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 73

Accepted Solution

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

0

LVL 14

Expert Comment

@sdstuber

Nice approach
0

LVL 73

Expert Comment

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

### Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.