Field SUM of two MSSQL Sub-consultations

Question

I need a field derived from two sub-consultations that gives the sum of values based on a field. Follow example:

SQL:

SELECT
    CodCliente
    ,DataVenda
    ,Vendedor
    ,Valor
    ,DataPagamento
    ,TotalCliente --> SOMA DE [Valor] de mesmo [CodCliente]

FROM

    ((SELECT
        CodCliente
        ,DataVenda
        ,Vendedor = 'Fulano'
        ,Valor
        ,DataPagamento

    FROM TabelaVendasFulano)

    UNION ALL

    (SELECT
        CodCliente
        ,DataVenda
        ,Vendedor = 'Ciclano'
        ,Valor
        ,DataPagamento

    FROM TabelaVendasCiclano)) Vendas

An example of the result I want:

+------------+-----------+----------+-------+---------------+--------------+
| CodCliente | DataVenda | Vendedor | Valor | DataPagamento | TotalCliente |
+------------+-----------+----------+-------+---------------+--------------+
|     01     |2014-01-02 |  Fulano  | 25,00 |  2014-01-05   |    105,00    |
|     01     |2014-02-07 |  Fulano  | 50,00 |  2014-02-10   |    105,00    |
|     01     |2014-02-16 |  Ciclano | 30,00 |  2014-02-20   |    105,00    |
|     02     |2014-01-20 |  Fulano  | 15,00 |  2014-01-25   |     70,00    |
|     02     |2014-03-05 |  Fulano  | 55,00 |  2014-03-10   |     70,00    |
|     03     |2014-03-12 |  Ciclano | 80,00 |  2014-03-20   |    140,00    |
|     03     |2014-07-25 |  Ciclano | 60,00 |  2014-07-30   |    140,00    |
+------------+-----------+----------+-------+---------------+--------------+

I want to know how to do this column Totalclient using the SQL I did, which is nothing more than the sum of the field [Valor] of the two sub-allocations based on the [CodCliente]

I know there are much simpler ways to solve this in this example, but I just used it to illustrate. I am using SQL Server.

Thank you!

Answer 1

You can use OVER(PARTITION BY ... ) in your query for this:

SELECT
    CodCliente
    ,DataVenda
    ,Vendedor
    ,Valor
    ,DataPagamento
    ,SUM(Valor) As TotalCliente OVER (PARTITION BY CodCliente)

FROM

    ((SELECT
        CodCliente
        ,DataVenda
        ,Vendedor = 'Fulano'
        ,Valor
        ,DataPagamento

    FROM TabelaVendasFulano)

    UNION ALL

    (SELECT
        CodCliente
        ,DataVenda
        ,Vendedor = 'Ciclano'
        ,Valor
        ,DataPagamento

    FROM TabelaVendasCiclano)) Vendas

In the case of a consultation like yours, I like to use a TEC (Common Table Expression)

WITH Vendas AS 
(SELECT
    CodCliente
    ,DataVenda
    ,Vendedor = 'Fulano'
    ,Valor
    ,DataPagamento    
FROM TabelaVendasFulano)

UNION ALL

(SELECT
    CodCliente
    ,DataVenda
    ,Vendedor = 'Ciclano'
    ,Valor
    ,DataPagamento
)

SELECT
    CodCliente
    ,DataVenda
    ,Vendedor
    ,Valor
    ,DataPagamento
    ,SUM(Valor) AS TotalCliente OVER (PARTITION BY CodCliente)