Write a SQL query?

Question

SQL

Good evening! Please help me to compose a SQL query (SQL Server 2008)
At the moment there is the following:

SELECT<br/>
 Data,<br/>
 Postav.Nazv,<br/>
 Schet.Shet,<br/>
 Schet.id,<br/>
 Schet.OtKogo,<br/>
 sum(Naimen.Kolvo*Naimen.Cena) AS SUMM,<br/>
 sum(Opl) AS SUMM2, <br/>
 (sum(Naimen.Kolvo*Naimen.Cena)-sum(Opl)) AS RAZN<br/>
FROM<br/>
 Schet,<br/>
 Postav,<br/>
 Naimen,<br/>
 Oplata<br/>
WHERE <br/>
 (Postav.id=Schet.OtKogo) AND <br/>
 (Naimen.Shet = Schet.id) AND<br/>
 (Oplata.Shet = Schet.id)<br/>
GROUP BY<br/>
 Schet.Data,<br/>
 Postav.Nazv,<br/>
 Schet.Shet,<br/>
 Schet.id,<br/>
 Schet.OtKogo<br/>

The problem is that it is not possible to normally display 3 counted fields.
In this variant, when adding any values ​​to the Oplata table, the SUMM field in the query begins to double.
The data schema of the database looks like this:

Naimen table values:

Oplata table values:

Query result in the current state:

Although the following values ​​should ideally be:
id SUMM SUMM2 RAZN
3 1250 0 1250
4 200 11111 10911
5 880 100 780
19 12321 100 12221
22 4653 .68 0 4653.68
Something like this in general. Thanks in advance!!!

Answer 1

this is how it should happen.
You select a line for each product, select a line from PAYMENT
and, accordingly, it is summed up as many times as you have product lines.
I recommend writing JOIN queries for easier reading.
If there can be several payments on an account, you need to use subqueries
1. Determine the account and subquery the total of receipts closing the account
2. determine its price = amount (total according to Naimen)
3. display the result
in real conditions, it ’s more correct to track accounts closed by payments
(at least on the basis of “the account is paid in full”)
In your scheme, you always carry out calculations across the entire database without restrictions ...
below is the query that will complete your task (not optimized):
Select QR1.*,
sum(Naimen.Kolvo*Naimen.Cena) AS SUMM,
sum(Naimen.Kolvo*Naimen.Cena) - QR.Payment AS RAZN
FROM (
SELECT
SCHET. Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo,
( Select sum(OPLATA.Opl) from Naimen Where Naimen.Shet = Schet.id ) As Payment
FROM Schet
JOIN Postav ON Postav.id=Schet. OtKogo
JOIN Oplata ON Oplata.Shet = Schet.id
GROUP BY
Schet.Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo
) QR1
JOIN Naimen ON Naimen.Shet = Schet.id
GROUP BY QR1.Data,
QR1.Nazv,
QR1.Shet,
QR1.id,
QR1.OtKogo,
QR1.Payment

Answer 2

I sincerely wanted to help you for 5 minutes, I didn’t understand what you all the same want.
I think the problem is that you need to use joins and join everything to the score, this should solve the problem of duplicates.
In general, the names of the fields are vyrviglaznye, it is unpleasant to read. For one Schet.shet, the one who will support this code will hate you, use one name.
And in general, start by reading books, it's boring but useful.

Answer 3

Somewhere so)

SELECT
  sc.data, 
  SUM(n.Kolvo * n.Cena) itogo,
  SUM(n.Kolvo * n.Cena)-IFNULL(o.opl,0) ostatok,
  o.opl,
  p.Nazv,
  sc.id
FROM
  habr.naimen n
  LEFT JOIN
  habr.schet sc
  ON sc.id = n.Schet
  LEFT JOIN habr.postav p
  ON p.id = sc.OtKogo
  LEFT JOIN habr.oplata o
  on o.Schet = sc.id
GROUP BY
  n.Schet

Answer 4

PS: the last QR1.Payment is redundant… it is meaningless

Answer 5

I would like the invoice to be displayed in the Shet tables with its amount, how much has already been paid for it and how much remained to be paid, according to the formulas:
Sum of all items (quantity * price)
Sum of all payments
The difference between the total amount of goods and how much has already been paid.
That is, the invoice consists of several items that have a price and quantity, we multiply the price and quantity and add up all the items related only to this account.
It's the same with payment, in the end we all withdraw it.

Answer 6

In particular, here is a piece that works great, but it calculates only one field "Amount", but it is necessary that the payment field also appear, the values ​​\u200b\u200bof which are in another plate, but everything is similar to this one.
SELECT
Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo,
sum(Naimen.Kolvo*Naimen.Cena) AS SUMM
FROM
Schet
LEFT OUTER JOIN Postav ON Postav.id=Schet.OtKogo
LEFT OUTER JOIN Naimen ON Naimen.Shet = Schet.id
LEFT OUTER JOIN Oplata ON Oplata.Shet = Schet.id
GROUP BY
Schet.Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo