CODEWITHSUNDEEP
mysqlsql
xrcwrn
xrcwrn

Reputation: 5327

sum(amount) from three tables returning null

I am having following tables 

create table supplier_paid_details(
    id bigint(10) NOT NULL AUTO_INCREMENT,
    payment_mode varchar(20),
    payment_date date,
    add_date timestamp DEFAULT 0,
    status varchar(20),
    supp_payment_id bigint(10) NOT NULL,
    primary Key(id),
    CONSTRAINT fk_paid FOREIGN KEY (supp_payment_id)
    REFERENCES supplier_payment_details(id)
    );

create table supplier_paid_check(
id bigint(10) NOT NULL AUTO_INCREMENT,
check_no bigint(10) NOT NULL, 
dated date,
payable_at varchar(50),
paid_to_acc_no varchar(30),
paid_to_bank varchar(30),
paid_to_branch varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_ckeck FOREIGN KEY (spd_id)
REFERENCES supplier_payment_details(id)
);



create table supplier_paid_online(
id bigint(10) NOT NULL AUTO_INCREMENT,
pay_ref_no varchar(50),
paid_from_bank varchar(50),
from_acc_no varchar(50),
paid_to_acc_no varchar(30),
paid_to_bank varchar(30),
paid_to_branch varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_online FOREIGN KEY (spd_id)
REFERENCES supplier_paid_details(id)
);

create table supplier_paid_cash(
id bigint(10) NOT NULL AUTO_INCREMENT,
to_person varchar(40),
designation varchar(40),
receipt_no varchar(30),
spd_id bigint(10),
add_date timestamp DEFAULT 0,
amount float(10,2),
status varchar(20),
primary Key(id),
CONSTRAINT fk_cash FOREIGN KEY (spd_id)
REFERENCES supplier_paid_details(id)
);

Here I would like to sum amounts from supplier_paid_check,supplier_paid_cash,supplier_paid_online.

for that I am using following query but this is showing null.

 select sum(ca.amount+ch.amount+onl.amount) as amount from
supplier_paid_details as pd
left join supplier_paid_cash as ca
on pd.id=ca.spd_id
left join supplier_paid_check as ch
on pd.id=ch.spd_id
left join supplier_paid_online as onl
on pd.id=onl.spd_id
where pd.supp_payment_id=1;

Upvotes: 0

Views: 84

Answers (2)

timo.rieber
timo.rieber

Reputation: 3867

I suppose there is at least one row in one of your three tables where amount has a NULL value. This leads to NULL as result in the end as the DBMS could not determine how to handle this value in combination with the other. But there is a way out: you could tell your DBMS what to do with NULL values. Use IFNULL.

select
    sum(IFNULL(ca.amount, 0) + IFNULL(ch.amount, 0) + IFNULL(onl.amount, 0)) as amount 
from
    supplier_paid_details as pd left join supplier_paid_cash as ca on pd.id=ca.spd_id
    left join supplier_paid_check as ch on pd.id=ch.spd_id
    left join supplier_paid_online as onl on pd.id=onl.spd_id
where
    pd.supp_payment_id=1;

Read more on IFNULL at MySQL Docs.

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

select sum(
              coalesce(ca.amount, 0)
            + coalesce(ch.amount, 0)
            + coalesce(onl.amount, 0)
          ) as amount from
....

or this:

select sum(ca.amount) + sum(ch.amount) + sum(onl.amount) as amount from
....

Upvotes: 0

Related Questions