Solved

Grouping two columns together

Posted on 2011-09-02
7
541 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
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
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 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to recover a database from a user managed backup
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.

862 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

25 Experts available now in Live!

Get 1:1 Help Now