CODEWITHSUNDEEP
sqloracle-databaseplsql
mayank
mayank

Reputation: 25

Grouping the data by (day) and then Join the 2 tables together in Oracle

There are Two tables licenses and organization

licenses table contains 5 columns

oracle_apps       tableau      sab_bi      tririga     time_snapshot
0                  1            1           1          2017-06-13 08:12:02.640
0                  0            0           1          2017-06-13 09:12:02.640
0                  0            1           0          2017-06-13 11:52:02.640
0                  1            0           1          2017-06-14 09:12:02.640   
0                  0            1           0          2017-06-14 10:12:02.640

organization table has 2 columns

license_name                 license_count
oracle_                      5.0000000
tableau_                     1.0000000
sab_                         20.0000000 
tririga_                     10.0000000

So Output will an select query or pl SQL stored procedure that will contain the join of both the tables and it will show the how many licenses are used in a day (count)

Output:

 oracle_apps    tableau    sab_bi    tririga    time_snapshot  oracle_    tableau_    sap_        tririga_  

 0              1          2         2          2017-06-13     5.0000000  1.0000000   20.0000000  10.0000000
 0              1          1         1          2017-06-14     5.0000000  1.0000000   20.0000000  10.0000000

For Ex: on 2017-06-13 total 5 licenses are used and individually 0,1,2,2 i.e. sum(oracle_apps), sum(tableau), sum(sab_bi), sum(tririga) and lly, on 2017-06-14 total 3 licenses are used i.e. (0, 1, 1, 1)

That thing was easy by using group by to_char(time_snapshot, yyyy-mm-dd) but I'm not able to join the tables and use row as a column because in organization table the row will column in output and I know one way to do it using PIVOT but I'm confused how to get that in outcome and for each and every row the license_name values will be same i.e. ( 5.0000000 | 1.0000000 | 20.0000000
| 10.0000000)

So is there a solution of Grouping the data and Joining the table together?

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I would recommend that you pivot the data before joining:

select l.dte, l.oracle_apps, l.tableau, l.sab_bi, l.tririga,,
       o.oracle_, o.tableau_, o.sab_, o.tririga_
from (select trunc(l.time_snapshot) as dte,
             sum(l.oracle_apps) as oracle_apps,
             sum(l.tableau) as tableau,
             sum(l.sab_bi) as sab_bi,
             sum(l.tririga) as tririga
      from licenses l
      group by trunc(l.time_snapshot)
     ) l cross join
     (select sum(case when license_name = 'oracle_' then license_count else 0 end) as oracle_,
             sum(case when license_name = 'tableau_' then license_count else 0 end) as tableau_,
             sum(case when license_name = 'sab_' then license_count else 0 end) as sab_,
             sum(case when license_name = 'tririga_' then license_count else 0 end) as tririga_        
      from organization o
     ) o;

Aggregating on the individual tables seems safer to me (if oracle_ appeared twice in the organization table, your license counts would be off). In addition, the resulting queries may be faster because optimizations on a single table are usually easier than on multiple tables.

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Use CROSS JOIN, SUM, MAX, CASE..WHEN..THEN.. and GROUP BY

SELECT trunc( l.time_snapshot) As time_snapshot,
       sum( l.oracle_apps ) As oracle_apps,
       sum( l.tableau ) As tableau,
       sum( l.sab_bi  ) As sab_bi,
       sum( l.tririga ) As tririga,
       max( CASE WHEN o.license_name = 'oracle_' THEN o.license_count END ) As oracle_,
       max( CASE WHEN o.license_name = 'tableau_' THEN o.license_count END ) As tableau_,
       max( CASE WHEN o.license_name = 'sab_' THEN o.license_count END ) As sab_,
       max( CASE WHEN o.license_name = 'tririga_' THEN o.license_count END ) As tririga_
FROM licenses l
CROSS JOIN organization o 
GROUP BY trunc( l.time_snapshot)

Demo: http://sqlfiddle.com/#!4/24b53/5

Upvotes: 2

Related Questions