Searching records from 2 tables with Local SQL gives double answers

Question

I have a problem which I try to solve with Local SQL, but I can't find any solution. Can anybody give me a hint?

I have 2 Paradox tables Art.DB and Bet.DB

Art has 4 columns:

Datum, Object, Nr, Summa

Datum        Object     Nr     Summa
------------------------------------
2023-01-01   Tools      100    5000
2023-01-01   Bags       100    1000
2023-02-01   Tyres      101    4000 
2023-03-01   Cart       102    5700

Bet has 3 columns:

Nr, Type, Summa   

Nr     Type    Summa
---------------------
100    Card    5000
100    Cash    1000 
101    Card    4000
102    Cash    5700

I want to summarize the sales receipts, which is Nr

The SQL I use is

SELECT
    a.Nr, SUM(Summa) AS Summa 
FROM
    Art AS a, Bet AS b 
WHERE
    a.Nr = b.Nr
GROUP BY
    a.nr 
ORDER BY
    Nr

My problem is that I get double Summa because 2 payments with different methods for Nr = 100

Nr    Summa
-----------
100   12000
101    4000
102    5700

I am expecting a sum of 6000 and not 12000 for Nr 100. I understand why I get this result, but how should I write the SQL statement to get the desired result?

Answer 1

You have not mentioned which summa column to be taken from Art or Bet. Change the sum according to your needs

here is the code

-- Calculate sum for each unique 'Nr' by joining 'Art' and 'Bet' tables
SELECT
    a.Nr,
    COALESCE(SUM(b.Summa), 0) AS TotalSum
FROM
    -- Subquery retrieves distinct 'Nr' values from 'Art'
    (SELECT DISTINCT Nr FROM Art) AS a
LEFT JOIN
    Bet AS b ON a.Nr = b.Nr
GROUP BY
    a.Nr
ORDER BY
    a.Nr;

Answer 2

Your tables as structured do not represent a coherent model. With no uniqueness constraint on [Nr] in either table, you cannot expect to execute a meaningful query.

For example, there is no way to tell whether (case 1) the tools were paid by card and the bags were paid by cash, or (case 2) the tools were paid by cash and the bags were paid by card. All four records are linked by the key value Nr.

Of course, a person could guess that the tools were paid by card and the bags were paid by cash by looking at the corresponding [Summa] values. But this will not always be possible because the values may not be different.

Besides, [Summa] is not part of the key of either table, and the BDE is not allowed to make guesses.

However, I am allowed to make guesses, so here is what I guess your table structure should be:

Art has 4 columns:

Datum, Object, Nr, Summa

    Datum        Object     Nr     Summa
    ------------------------------------
    2023-01-01   Tools      100    5000
    2023-01-01   Bags       101    1000
    2023-02-01   Tyres      100    4000 
    2023-03-01   Cart       101    5700

Bet has 2 columns:

Nr, Type   

Nr     Type
-----------
100    Card
101    Cash 

Summa values must not appear in more than one table.

Bet is a lookup table that holds only the payment type definitions.

[Nr] in Bet is constrained to be unique.

[Nr] in Art defines the payment type for each record.

This data model is far from complete. In particular, Bet needs to have a unique primary key because otherwise duplicate records can appear.