CODEWITHSUNDEEP
mysqlsql
Tristan Ross
Tristan Ross

Reputation: 117

SUM function add records twice

I have 2 records amount in my database and i have to use sum function to display the total of amount but it shows and sums twice

I have tried the other sum function in the same query and its working but when i use it second time, it sums twice.

$sql = "SELECT 
                mm.idno as idno,
                TRIM(UPPER(CONCAT(mm.fname, ' ', mm.mname, ' ', mm.lname))) as fullname, 
                TRIM(UPPER(mm.branchname)) as branchname, 
                TRIM(UPPER(mm.address)) as outlet_address, 
                SUM(dr.totalamt) as total_amount,
                SUM(drr.totalamt) as return_amount  
        FROM 8_membermain mm
            LEFT JOIN 8_directsalessummary dr ON mm.idno = dr.idno
            LEFT JOIN 8_drreturnsummary drr ON dr.idno = drr.idno
        WHERE mm.status > 0 AND dr.status = 1
        AND dr.ispaid = 'Full Payment'
        AND dr.trandate BETWEEN '".$date1."' AND '".$date2."' 
        AND drr.trandate BETWEEN '".$date1."' AND '".$date2."'";

SUM(dr.totalamt) as total_amount works fine. The only problem is SUM(drr.totalamt) as return_amount

Upvotes: 0

Views: 1890

Answers (2)

ysth
ysth

Reputation: 98398

From your description of the problem, it sounds like your query is matching two dr rows and one drr row. If there had been more than one drr row, the dr total would have been wrong as well. This is because joins return all possible combinations of the joined tables. For example, if there were two dr rows, that we will call A and B, and three drr rows C, D, and E, the results (before grouping together into a single row, due to the use of sum with no group by) would be:

A and C
A and D
A and E
B and C
B and D
B and E

When summing the dr column, the values for A and B are each encountered three times, resulting in a tripled sum . And when summing the drr column, the values for C, D, and E are each encountered two times, resulting in a doubled sum.

The proper fix for this is to join subqueries for dr and drr, as shown in Tim's answer. But sometimes, where there are very few tables involved and very few records in each grouping for most of them and the tables have primary keys or some other unique column, you can simply divide each sum by the number of rows in each other table (or 1 if there were no rows):

SELECT
    ...
    SUM(dr.totalamt)/GREATEST(1,COUNT(DISTINCT drr.primarykey)) as total_amount,
    SUM(drr.totalamt)/GREATEST(1,COUNT(DISTINCT dr.primarykey)) as return_amount

A couple other comments on your query:

It seems like you are intending there to be a GROUP BY mm.idno at the end; without that, your query is returning data for mm-related fields for an arbitrary mm record that matched your where criteria, and the above trick will not work correctly if in fact your query finds multiple mm records.

You use left joins, but then in the where clause specify conditions that will be false if no row is found; this in effect makes your left joins be inner joins. If you want to find mm rows even with no dr or drr row, you need to move the conditions into the join, like:

LEFT JOIN 8_directsalessummary dr ON mm.idno = dr.idno
    AND dr.status = 1
    AND dr.ispaid = 'Full Payment'
    AND dr.trandate BETWEEN ...

Also, you are joining drr using dr.idno; if you wish to find drr records even if there is no dr record, you should use mm.idno instead:

LEFT JOIN 8_drreturnsummary drr ON mm.idno = drr.idno
    AND drr.trandate BETWEEN ...

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

The problem with your approach is that your are joining twice first, and then aggregating afterwards. The SUM(dr.totalamt) works because it only involves the first join. But SUM(drr.totalamt) is not correct, because you are overcounting due to the second join. To fix this, You should perform the aggregations in separate subqueries, and then to join to those subqueries:

SELECT
    mm.idno,
    TRIM(UPPER(CONCAT(mm.fname, ' ', mm.mname, ' ', mm.lname))) AS fullname,
    TRIM(UPPER(mm.branchname)) AS branchname,
    TRIM(UPPER(mm.address)) AS outlet_address, 
    COALESCE(dr.totalamt, 0) AS total_amount,
    COALESCE(drr.totalamt, 0) AS return_amount
FROM 8_membermain mm
LEFT JOIN
(
    SELECT idno, SUM(totalamt) AS totalamt
    FROM 8_directsalessummary
    WHERE status = 1 AND ispaid = 'Full Payment' AND
        trandate BETWEEN ? AND ?
    GROUP BY idno
) dr
    ON mm.idno = dr.idno
LEFT JOIN
(
    SELECT idno, SUM(totalamt) AS totalamt
    FROM 8_drreturnsummary
    WHERE trandate BETWEEN ? and ?
    GROUP BY idno
) drr
    ON mm.idno = drr.idno
WHERE
    mm.status > 0;

To the ? placeholders you would need to bind the two dates values $date1 and $date2. But, an additional explanation of how to use prepared statements in PHP seems too broad, and the problem with your query logic seems like the bigger issue.

Upvotes: 2

Related Questions