Group [group by] SQL Firebird

Asked

Viewed 621 times

0

I wonder if there is a way I can make a query where I can bring fields that are not in my group by.

I have the following code:

select sum(TAB_FATURAMENTO.vl_item),CLIENTE.insc_cnpj
from TAB_FATURAMENTO
inner join (IMP_PROCESSO
  inner join CLIENTE
  on IMP_PROCESSO.cd_cliente = CLIENTE.codigo)
on TAB_FATURAMENTO.cd_processo = IMP_PROCESSO.cd_processo
where TAB_FATURAMENTO.cd_cliente like '%'
and TAB_FATURAMENTO.pg='S'
group by CLIENTE.insc_cnpj

In my select I would like to bring two more fields, but the grouping I want to stay only by customers CNPJ.

sql select firebird group-by

  • What fields? Would be different values within the same client?

  • Yes, it would be different values, @Rovannlinhalis. In this case, I have clients with two CNPJ’s with the same reason, with different addresses, as well as the opposite.

  • 1

    I see no problem, already tried to put sum(X), cnpj, razao, endereco with group by cnpj, razao, endereco I believe it will be the way you need it

2 answers

1

It does, but only if you use aggregated functions in those values you want to return, like sum(),Count(),max() and etcs.

0


Thanks in advance for all your help.

In conversation with a co-worker, I explained my problem and the same gave me the following suggestion:

Do the select the way it is, and in the php page itself perform another select bringing the other information I need.

The same even suggested me perform this second select within a for, so consultation becomes faster.

<?php
    $tl_saldo = 0;
    if($_SESSION['saldo'] <> ""){
       $tl_saldo = count($_SESSION['saldo']);
    }
?>
<table width='100%' border='1'>
  <tr align='center' bgcolor='#999'>
    <td>CLIENTE</td>
    <td>CNPJ</td>
    <td>VL DESPESA</td>
  </tr>
<?php
    for($i = 0; $i < $tl_saldo; $i++){
        $obj_cnpj = $obj_pdo->getCnpj($_SESSION['saldo'][$i]->INSC_CNPJ);
        $_SESSION['cnpj'] = $obj_cnpj;
?>
  <tr align='center'>
    <td class='texto' style='text-align:left;text-indent:10px;'> print substr($_SESSION['cnpj'][0]->NOME,0,18); </td>
    <td class='texto'> print $_SESSION['saldo'][$i]->INSC_CNPJ; </td>
    <td class='texto'> print number_format($_SESSION['saldo'][$i]->SUM,"2",",","."); </td>
  </tr>
<?php
    }
?>
</table>
  • 1

    Dude, I didn’t even need all this. As the column you see wants to show is the client’s cnpj/Cpf, client’s name and value. You can put in the group by the other columns and then sum in the value. It will work the same way.

  • @Marcosmarques, the problem is that before we took over this bank, it was managed by a third party and this left open to the users the part of registration, so we have customer registrations with the same CNPJ with different names (but almost similar). That’s why we had all this work. Despite everything the same worked very well.

Browser other questions tagged sql select firebird group-by

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