1

Topic: Count of debts under the written out accounts

Something I rested and I do not understand where further to move, like absolutely simple request (
There is a table ` sht ` - accounts, and ` pp ` - payment orders. Under each account which is in operation, there can be some payment orders, or any.
It is necessary to select accounts behind which the debt is registered, i.e. the total of the account is more than total of the pay-sheets which have arrived on this account.
Request:

SELECT
SELECT sht.sht_no, client, status, sht_sum, SUM (pp.pl_sum)
FROM sht RIGHT JOIN pp USING (sht_no)
WHERE SHT.status> 0 # here simply means that the account "in operation"
GROUP BY pp.sht_no # here like as the grouping for operation SUM but if in the table pp still there are no pay-sheets under these accounts it turns out they is necessary fall out
HAVING SHT.sht_sum> SUM (pp.pl_sum)

Deduces only those accounts on which there is at least one payment.
How to deduce as well on what there is no pay-sheet?

2

Re: Count of debts under the written out accounts

Greider;
RIGHT replace on LEFT

3

Re: Count of debts under the written out accounts

miksoft wrote:

Greider;
RIGHT replace on LEFT

Yes I with LEFT also began actually... The result is identical.

4

Re: Count of debts under the written out accounts

SELECT sht.sht_no, sht.client, sht.status, sht.sht_sum, COALESCE (SUM (pp.pl_sum), 0)
FROM sht
LEFT JOIN pp USING (sht_no)
WHERE sht.status> 0
HAVING sht.sht_sum> COALESCE (SUM (pp.pl_sum), 0)

?

5

Re: Count of debts under the written out accounts

... "Function MySQL COALESCE returns the first nonzero expression in the list." And me it is necessary on the contrary that null-values SUM got to the list also. In such type result of 0 lines... Made while two  and UNION (in the table of accounts there is some redundancy in the form of number of the first pay-sheet though it and it is not so correct likely), works well though certainly one to make more interesting...

6

Re: Count of debts under the written out accounts

Greider wrote:

"Function MySQL COALESCE returns the first nonzero expression in the list."

Where you subtracted such delirium?

7

Re: Count of debts under the written out accounts

Akina wrote:

Where you such delirium subtracted?

Under the first link which has fallen out ... Now here here esteemed and understood that meant. Tried once again - the result is (for 6.48 seconds), but only one not paid account, and them about ten was added... And if to leave line GROUP BY pp.sht_no. Without it deduces nothing...

8

Re: Count of debts under the written out accounts

Horror what... And the official documentation to read - not our method?

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce wrote:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

Greider wrote:

and if to leave line GROUP BY pp.sht_no

Yes, I forgot to interpose it into the code, and without it SUM counts .

SELECT sht.sht_no, sht.client, sht.status, sht.sht_sum, COALESCE (SUM (pp.pl_sum), 0)
FROM sht
LEFT JOIN pp USING (sht_no)
WHERE sht.status> 0
GROUP BY sht.sht_no/*, sht.client, sht.status, sht.sht_sum */
HAVING sht.sht_sum> COALESCE (SUM (pp.pl_sum), 0)

And still a trifle - what type at fields sht.sht_sum and pp.pl_sum? If not DECIMAL certain problems are possible...

9

Re: Count of debts under the written out accounts

Akina, many thanks (once again)!
Really, business was in GROUP BY! It was necessary to group and in number from sht, and from pp
Here so all works as it is necessary:

SELECT sht.sht_no, sht.client, sht.status, sht.sht_sum, COALESCE (SUM (pp.pl_sum), 0) AS psum
FROM sht LEFT JOIN pp USING (sht_no)
WHERE sht.status> 0
GROUP BY pp.sht_no, sht.sht_no
HAVING sht.sht_sum> psum

It is possible still IFNULL as I understood to use
p.s. Yes, fields with the totals all in DECIMAL