how to select sum this month from left join three tables

It’s currently March 2021 and I’m trying to select sum from three tables with case if not this month and this year the output is 0.

the correct output when I do manual calculations is and the output that i want:

Alkohol 70% 1000 ML   0 
Amoxicillin 500 mg    0 
Amoxicillin Syrup     0  
Asam Askorbat         0  
Combiven              0  
Masker                100

I have tried this, but the output is not what I want:

SELECT table_one.name, CASE WHEN (MONTH(table_two.date) = MONTH(NOW())) AND (YEAR(table_two.date) = YEAR(NOW()))
       THEN SUM(income)
       ELSE 0
END AS total_income
FROM table_one
LEFT JOIN table_three ON table_one.id_one = table_three.one_id
LEFT JOIN table_two ON table_two.id_two = table_three.one_id
WHERE
(id_one NOT IN (SELECT one_id FROM table_three)
OR id_one IN (SELECT one_id FROM table_three))
GROUP BY one_id

here is my data and structure:

CREATE TABLE table_one (
  id_one INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(255) NOT NULL,
  unit VARCHAR(255) NOT NULL,
  stock INT(11) NOT NULL,
  PRIMARY KEY (id_one)
);

INSERT  INTO table_one VALUES 
(2,'Alkohol 70% 1000 ML','Botol',50),
(3,'Amoxicillin 500 mg','Tablet',0),
(4,'Amoxicillin Syrup','Botol',75),
(5,'Asam Askorbat','Tablet',0),
(6,'Combiven','Sup',25),
(16,'Masker','Tube',200);

CREATE TABLE table_three (
  id_three INT(11) NOT NULL AUTO_INCREMENT,
  batch CHAR(1) NOT NULL,
  income INT(11) NOT NULL,
  exp_date DATE NOT NULL,
  descr TEXT DEFAULT NULL,
  one_id INT(11) NOT NULL,
  two_id INT(11) NOT NULL,
  PRIMARY KEY (id_three),
  KEY one_id (one_id),
  KEY two_id (two_id),
  CONSTRAINT table_three_ibfk_1 FOREIGN KEY (one_id) REFERENCES table_one (id_one),
  CONSTRAINT table_three_ibfk_2 FOREIGN KEY (two_id) REFERENCES table_two (id_two)
);


INSERT  INTO table_three VALUES 
(1,'A',1000,'2022-02-22','ket.',6,1),
(2,'1',500,'2021-02-22','',2,4),
(3,'4',50,'1970-01-01','',5,5),
(4,'7',75,'1970-01-01','',4,5),
(5,'1',100,'1970-01-01','',6,4),
(6,'',100,'1970-01-01','',16,6),
(7,'',100,'1970-01-01','',16,5),
(8,'',50,'1970-01-01','',16,7),
(11,'',50,'0000-00-00',NULL,16,7);

/*Table structure for table table_two */

CREATE TABLE table_two (
  id_two INT(11) NOT NULL AUTO_INCREMENT,
  DATE DATE NOT NULL,
  source VARCHAR(64) NOT NULL,
  file_source TEXT NOT NULL,
  descr TEXT DEFAULT NULL,
  PRIMARY KEY (id_two)
);

INSERT  INTO table_two VALUES 
(1,'2021-02-18','Lain-lain','bukti barang masuk_3.jpg','test'),
(4,'2021-02-18','Pembelian dengan dana JKN','bukti barang masuk_2.jpg','keterangan'),
(5,'2021-02-21','Dinas kesehatan','bukti barang masuk_1.jpg','set'),
(6,'2021-02-26','Pembelian dengan dana JKN','bukti barang masuk.jpg',''),
(7,'2021-03-02','Pembelian dengan dana JKN','4_faktur pembelian.jpg','');

what should I do?

Answer

Here is the sql request you want :

SELECT 
    `table_one`.`NAME`, 
    SUM(CASE WHEN (MONTH(`table_two`.`DATE`) = MONTH(NOW())) AND (YEAR(`table_two`.`DATE`) = YEAR(NOW()))
       THEN `table_three`.`income`
       ELSE 0
       END
    ) AS total_income
FROM `table_one`
LEFT JOIN `table_three` ON `table_three`.`one_id` = `table_one`.`id_one`
LEFT JOIN `table_two` ON `table_two`.`id_two` = `table_three`.`two_id`
GROUP BY `table_one`.`id_one`

You were very close

The main part was to SUM after the action of CASE.

Leave a Reply

Your email address will not be published. Required fields are marked *