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!