Merge 2 Oracle SELECTS and as a result 2 columns with different values

Question

Merge 2 Oracle SELECTS and as a result 2 columns with different values

Asked 5 years, 8 months ago

Viewed 1,978 times

2

I am facing a problem, I have two selects sql

select *
from web.demonstrativo_processados 
where nroempresa = 1
  and data between to_date('2018/01/01' , 'yyyy/mm/dd')
  and to_date('2018/12/31' , 'yyyy/mm/dd')

that brings me the values of the year 2018 and I have this:

select * 
from web.demonstrativo_processados 
where nroempresa = 1
  and data between to_date('2017/01/01' , 'yyyy/mm/dd')
  and to_date('2017/12/31' , 'yyyy/mm/dd')

Which returns the 2017 values.

I would like you to answer these two columns, so I need to integrate the 2 selects.

Since I’m using php and google Charts to generate graphics, it would make my job a lot easier. graph that I intend to make

Thank you

WHILE OF MY PHP

    <html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      google.charts.load('current', {'packages':['corechart']});
      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['Mês', '2017', '2018'],


          <?php

            include ("./conexao_local.php");

            $sql = "select * from web.demonstrativo_processados where nroempresa = 1 and data between to_date('2017/01/01' , 'yyyy/mm/dd') and to_date('2017/12/31' , 'yyyy/mm/dd')";
            $stmt = oci_parse($conexao, $sql);

              oci_execute($stmt);

          while (($array = oci_fetch_array($stmt, OCI_BOTH)) != false) {
           $valorvenda = $array["VLRVENDA"];


           $valorvenda1 = str_replace(",",".", $valorvenda);

           ?>                       


          ['Mês 2017',  <?php echo $valorvenda1 ?>,400],
         <?php } ?>
        ]);

        var options = {
          title: 'Company Performance',
          hAxis: {title: 'Year',  titleTextStyle: {color: '#333'}},
          vAxis: {minValue: 0}
        };

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div_1'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div_1" style="width: 100%; height: 500px;"></div>

  </body>
</html>

Which data do you want to bring from 2018 and 2017, quantity? or all data?

– Marciano Machado

2018/10/26 at 18:30
all, I filter in php

– Ricardo

2018/10/26 at 18:36
but joining the lines by date?

– Marciano Machado

2018/10/26 at 18:37
Why separate the data by column/year if you already have date field in the records? You will have several null fields per number of years, and will still make it difficult to filter in PHP. The ideal would be to know the field related to the value you want, and make a COUNT, SUM, etc., after GROUP BY. Try to improve your question, enter more details as it became wide.

– rbz

2018/10/26 at 18:43
Do you have to add up the value per year? is that it? vc want YEAR column and VLRVENDA SOMA column?

– rbz

2018/10/26 at 18:51
do not need to add anything Rbz, I just want to come the two columns, without sum with nothing, that then I get there in php and I can generate the graph that I put in the question

– Ricardo

2018/10/26 at 18:53
CHAT

– rbz

2018/10/26 at 18:59

Show 2 more comments

3 answers

Browser other questions tagged php oracle

You are not signed in. Login or sign up in order to post.

by rbz • **9,949** points · Answer 1 · 2018-10-26T18:28:37+00:00

Edit #1

SELECT data, VLRVENDAS
FROM web.demonstrativo_processados 
WHERE nroempresa = 1 
AND data between to_date('2017/01/01' , 'yyyy/mm/dd') 
AND to_date('2018/12/31' , 'yyyy/mm/dd')

With UNION:

select * from (
(
select ANO = '2018', * 
from web.demonstrativo_processados 
where nroempresa = 1 
and data between to_date('2018/01/01' , 'yyyy/mm/dd') 
and to_date('2018/12/31' , 'yyyy/mm/dd')
)
union all
(
select ANO = '2017', * 
from web.demonstrativo_processados 
where nroempresa = 1 
and data between to_date('2017/01/01' , 'yyyy/mm/dd') 
and to_date('2017/12/31' , 'yyyy/mm/dd')
) as aliastab

With OR and YEAR:

select YEAR(data), * 
from web.demonstrativo_processados 
where nroempresa = 1 
and ( (data between to_date('2018/01/01' , 'yyyy/mm/dd') 
and to_date('2018/12/31' , 'yyyy/mm/dd')) 
OR (data between to_date('2017/01/01' , 'yyyy/mm/dd') 
and to_date('2017/12/31' , 'yyyy/mm/dd')) )

by Rodrigo Rocha • **191** points · Answer 2 · 2018-10-26T18:37:10+00:00

Assuming you want to calculate the 2018 total and 2019 total, follow the code using a 2018 and 2017 consultation.

  SELECT (select SUM(valor)
           from web.demonstrativo_processados 
           where nroempresa = 1
           and data between to_date('2018/01/01' , 'yyyy/mm/dd')
           and to_date('2018/12/31' , 'yyyy/mm/dd')) as Total2018
          ,(select SUM(valor)
           from web.demonstrativo_processados 
           where nroempresa = 1
           and data between to_date('2017/01/01' , 'yyyy/mm/dd')
           and to_date('2017/12/31' , 'yyyy/mm/dd')) as Total2017

by Ricardo • 82 points · Answer 3 · 2018-10-27T14:28:38+00:00

I managed to make

select a.vlrvenda as venda2017, b.vlrvenda as venda2018
  from web.demonstrativo_processados a, web.demonstrativo_processados b
 where a.nroempresa = 1
   and a.data between to_date('2017/01/01', 'yyyy/mm/dd') and
       to_date('2017/12/31', 'yyyy/mm/dd')
   and a.nroempresa = b.nroempresa
   and b.data between to_date('2018/01/01', 'yyyy/mm/dd') and
       to_date('2018/12/31', 'yyyy/mm/dd')
   and to_char(trunc(a.data, 'MM'), 'MON') = to_char(trunc(b.data, 'MM'), 'MON')