Solved

Grouping two columns together

Posted on 2011-09-02
7
539 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
@sdstuber

Nice approach
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now