4
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!
It’s SQL Server friend?
– gmsantos
Oops, it is. I forgot to specify.
– Cassio Milanelo
You can [Edit] your question and include the tag to facilitate google searches.
– gmsantos
It is in SQL Server 2008 is more boring to do what you want rs
– gmsantos
How would you do in 2012, out of curiosity? (It won’t solve my problem, but it’s good to know)
– Cassio Milanelo
Would it be something with PARTITION BY ... OVER (Calm down, it looks like it’s gonna work)
– gmsantos