Column outside the WHERE condition of SELECT

Navigation
2

I need to make a query that returns a column with the amount of requests the customer made within the period spent in WHERE and together with another column with the total number of requests that the customer has made so far. More or less like this: 

[CLIENTE][COMPRAS NO PERÍODO][COMPRAS NO TOTAL]
 Marcos           5                 28
 Flávio           7                 35
 Roberta          9                 32

How would I do this?

Thank you!

EDIT

Here is the SQL that I am using for the tests. What I really want is the [Full Title] field with the same idea I said above: (Ah, detail: I can not change the DB structure, it is already deployed) 

SELECT CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem 'OP'
        ,NotasFiscais.DataEmissao 'Data Emissão'
        ,NotasFiscais.NumNota 'Nota'
        ,OrcHdr.NomeCliente 'Cliente'
        ,NotasFiscais.ValorTotalNota 'Valor NF'
        ,'PercComissao' = CASE WHEN Natureza LIKE 'C' AND OrdensProducao.Cancelado = 0 
                          THEN SUM(Valor/SPreco) END
        ,'Titulo Período' = COUNT(FIN_Titulos.IdTipoDocumento)
        ,'Titulo Total' = ???

FROM OrcHdr 
INNER JOIN OrdensProducao ON OrcHdr.NumOrdem = OrdensProducao.NumOrdem
INNER JOIN CustosVendaItemPed ON OrdensProducao.NumOrdem = CustosVendaItemPed.NumPedido
INNER JOIN ItemNota ON OrdensProducao.NumOrdem = ItemNota.NumOrdem 
INNER JOIN NotasFiscais ON ItemNota.ObjID_Nota = NotasFiscais.ObjID
INNER JOIN FIN_Titulos ON NotasFiscais.ObjID = FIN_Titulos.ObjIDDoctoOrigem

WHERE CustosVendaItemPed.Descricao NOT LIKE 'CIN%' 
AND CustosVendaItemPed.Descricao NOT LIKE 'Centro Integrado%' 
AND CustosVendaItemPed.Descricao NOT LIKE 'não pagar%' 
AND NotasFiscais.Situacao LIKE 'N' 
AND ItemNota.Fatura LIKE 'F' 
AND ItemNota.Devolucao NOT LIKE 'D' 
AND NotasFiscais.NaturezaOperacao NOT LIKE 'reme%' 
AND NotasFiscais.NaturezaOperacao NOT LIKE 'doa%' 
AND FIN_Titulos.DebCred LIKE 'C' 
AND FIN_Titulos.Situacao <> 3 
AND DataVencimento >= '2012-11-01 00:00:00.000' 
AND DataVencimento <= '2012-11-30 00:00:00.000')

GROUP BY 
        CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem
        ,NotasFiscais.DataEmissao
        ,NotasFiscais.NumNota
        ,OrcHdr.NomeCliente
        ,NotasFiscais.ValorTotalNota
        ,CustosVendaItemPed.Natureza
        ,OrdensProducao.Cancelado
        ,DataVencimento

Order By 
        CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem
    
asked by anonymous 18.12.2014 / 19:11

3 answers

4

Another option would be to use CROSS APPLY or OUTER APPLY , both execute a function or query on the result of a select , for example:

Consider the tables 

Tabela Cliente
| Id | Nome  |
|  1 | Pedro |
|  2 | João  |

Tabela Compra
| IdCliente | Produto |
|     1     | Arroz   |
|     1     | Feijão  |

To know how many products each customer bought, you could do this: 

SELECT
    cli.Id,
    cli.Nome,
    prd.Qtde
FROM
    Clientes cli -- select principal
    OUTER APPLY (SELECT COUNT(*) Qtde 
                 FROM Compra cmp 
                 WHERE (cmp.IdCliente = cli.Id)) prd -- select secundário

The result would be: 

| Id | Nome  | Qtde |
|  1 | Pedro |   2  |
|  2 | João  |   0  |

CROSS APPLY and OUTER APPLY are similar to INNER JOIN and LEFT JOIN , respectively, because CROSS causes only common records between main and select the secondary select is displayed, other than the OUTER , which returns all the primary select records and the secondary select records found.

For this example above, you could use CROSS or OUTER , as there will always be a secondary select record.

  

Notes: The tables in the examples were simplified to make it easier to understand and the main and secondary select only served to link the example with the text .

If you want to know a little more about CROSS and JOIN , you can take a look at in this link .

    
19.12.2014 / 00:19
2

The solution to your case is to use a subquery, getting it as follows. 

,'Titulo Total' = (SELECT COUNT(*) 
                   FROM FIN_Titulos TitulosTotal 
                   WHERE NotasFiscais.ObjID = TitulosTotal.ObjIDDoctoOrigem)
    
18.12.2014 / 19:16
2

I made my SELECT like this: 

SELECT CustosVendaItemPed.CodConta
        ,CustosVendaItemPed.Descricao
        ,OrdensProducao.NumOrdem 'OP'
        ,NotasFiscais.DataEmissao 'Data Emissão'
        ,NotasFiscais.NumNota 'Nota'
        ,OrcHdr.NomeCliente 'Cliente'
        ,NotasFiscais.ValorTotalNota 'Valor NF'
        ,'PercComissao' = CASE WHEN Natureza LIKE 'C' AND OrdensProducao.Cancelado = 0 
                          THEN SUM(Valor/SPreco) END
        ,'Titulo Período' = COUNT(FIN_Titulos.IdTipoDocumento)
        ,'Titulo Total' = (SELECT COUNT(*) 
                           FROM FIN_Titulos TitulosTotal 
                           WHERE NotasFiscais.ObjID = TitulosTotal.ObjIDDoctoOrigem)

Thank you!

    
18.12.2014 / 19:55
Convert hexadecimal to integer Take accentuation of a string