Summarize fields with direct calculations in sql query

Asked

Viewed 172 times

1

I don’t know how to do it I’ve tried almost all day and I can’t if any can help...

The problem is I have to add a field that is sine calculated in the direct sql query.

Sql:

SELECT
 CLIENTES.ID_CLIENTE,
 CLIENTES.CLIENTE,
 SUM(DAV_ITENS.QUANTIDADE) AS QUANTIDADES,
 SUM(DAV_ITENS.VALOR_CUSTO * DAV_ITENS.QUANTIDADE) AS VALOR_CUSTO,
 SUM(DAV_ITENS.VALOR_TOTAL) AS VALOR_TOTAL
FROM DAV
 LEFT OUTER JOIN DAV_ITENS ON (DAV.ID = DAV_ITENS.ID_DAV)
 INNER JOIN CLIENTES ON (CLIENTES.ID_CLIENTE = DAV.ID_CLIENTE)
GROUP BY
 CLIENTES.ID_CLIENTE,
 CLIENTES.CLIENTE, 
 DAV_ITENS.QUANTIDADE,
 DAV_ITENS.VALOR_CUSTO,
 DAV_ITENS.VALOR_TOTAL

The purpose of this query is to add up all the cost price of all the products a customer has bought. Only he is not doing it.

Esse é o resultado da consulta SQL

what I need is:

Ex: Customer 76 has a purchase with two products and for each one they buy three units of the same item the first item the sum of 26.64 and the second 334.80 only that I need it to add these two values so that the line of the stay so?

| id |            nome | QUANT | VALOR_CUSTO  | VALOR_VENDA  |
|----|-----------------|-------|--------------|--------------|
| 76 |         Eduardo |     6 |       364.44 |          873 |
| 20 |         Janaína |   4.2 |           11 |        138,6 |
| 10 |         Zé lelé | 40.25 |            5 |       603,75 |
sql firebird
  • 3

    You should not put in the GROUP BY clause the fields that are in the aggregation functions. Remove the fields DAV_ITENS.QUANTIDADE, DAV_ITENS.VALOR_CUSTO and DAV_ITENS.VALOR_TOTAL.

1 answer

2


Sql:

SELECT
 CLIENTES.ID_CLIENTE,
 CLIENTES.CLIENTE,
 SUM(DAV_ITENS.QUANTIDADE) AS QUANTIDADES,
 SUM(DAV_ITENS.VALOR_CUSTO * DAV_ITENS.QUANTIDADE) AS VALOR_CUSTO,
 SUM(DAV_ITENS.VALOR_TOTAL) AS VALOR_TOTAL
FROM DAV
 LEFT OUTER JOIN DAV_ITENS ON (DAV.ID = DAV_ITENS.ID_DAV)
 RIGHT OUTER JOIN PRODUTOS ON (DAV_ITENS.ID_PRODUTO = PRODUTOS.ID_PRODUTO)
 INNER JOIN CLIENTES ON (CLIENTES.ID_CLIENTE = DAV.ID_CLIENTE)
GROUP BY
 CLIENTES.ID_CLIENTE,
 CLIENTES.CLIENTE

In your case you should not add the fields DAV_ITENS.QUANTIDADE, DAV_ITENS.VALOR_CUSTO, DAV_ITENS.VALOR_TOTAL, as these fields will be used in the function SUM(), as using the GROUP BY shall be grouped by common values. Like the fields DAV_ITENS.QUANTIDADE, DAV_ITENS.VALOR_CUSTO, DAV_ITENS.VALOR_TOTAL has distinct values he did not make the correct sum.

I hope I’ve helped.

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.