Please help query this database to get SUM point-CodePudding

I have table:

nimhstrkrs	thsmstrkrs	kdkmktrkrs	sksmktrkrs
20522001	20212	CODEA	2
20522001	20212	CODEB	3
20522001	20212	CODEC	4
20522001	20211	CODED	2 (double)
20522001	20212	CODED	2 (double)
20522001	20212	CODEE	2
20522002	20211	CODEZ	5 (double)
20522002	20212	CODEZ	5 (double)
20522002	20212	CODEB	3
20522002	20212	CODEC	4
20522002	20212	CODED	2
20522002	20212	CODEE	2

I need result the double data only one in the sum:

$this-> getskstotal('20522001') result: 13
$this-> getskstotal('20522002') result: 16

My controller in codeigniter v3 is:

public function getskstotal($nim)
{
        $query = $this->db->query("select SUM(sksmktrkrs) as skstotal FROM rtrkrs WHERE nimhstrkrs = '".$nim."' GROUP BY kdkmktrkrs");
        foreach($query->result() as $value){
            $sks = $value->skstotal;
        }
        return $sks;    
}

but the result is not the same i need.

CodePudding user response：

You should distinct the results first and then make the sum.

select sum(sksmktrkrs) 
from (selec distinct nimhstrkrs, sksmktrkrs  from rtrkrs  WHERE nimhstrkrs = '".$nim."') 
GROUP BY kdkmktrkrs

CodePudding user response：

I think this would work for you: Demo

select
    sum("sksmktrkrs") 
from
    (
        select distinct
            "nimhstrkrs",
            "kdkmktrkrs",
            "sksmktrkrs" 
        from
            rtrkrs 
        WHERE
            "nimhstrkrs" = '".$nim."'
    )
;