Field SUM of two Subqueries in MSSQL

4

I'm needing a field derived from two subqueries that gives the sum of values based on a field. Here's an 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 CustomerClient using the SQL I did, which is nothing more than the sum of the [Valor] field of the two Subqueries based on the [CodCliente]

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

Thank you!

    
asked by anonymous 09.02.2015 / 12:54

1 answer

2

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 cases of a query like yours, I like to use a CTE (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)
    
09.02.2015 / 13:15