I have a sales table with columns:
CREATE TABLE SALES (
SALESID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(255) NOT NULL,
QUANTITY INT,
SALESDATE DATE NOT NULL DEFAULT (CURRENT_DATE),
PRIMARY KEY (SALESID)
);
In this table we entry data for those dates when there are sales records. The dates for which there is no sales no data will be there. Sample entry data:
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',3, '2024-10-23');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',2, '2024-10-23');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',7, '2024-10-21');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',5, '2024-10-21');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('XEPRAMYCIN',4, '2024-10-20');
INSERT INTO SALES (NAME, QUANTITY, SALESDATE) VALUES ('FEVOUT',4, '2024-10-20');
Now if a user wants to check the sales data for the medicine ‘XEPRAMYCIN‘ from ‘2024-10-20‘ to ‘2024-10-23‘, there will be no rows of data for the date 2024-10-22.
But I need to show all the dates that occurs between the selected date by the user and if there are no records in those dates it should show 0. How can I do that?
SELECT `NAME`, `QUANTITY`, `SALESDATE` FROM `SALES` WHERE DATE(`SALESDATE`) BETWEEN '2024-10-20' AND '2024-10-23';
Desired output::
NAME QUANTITY SALESDATE
XEPRAMYCIN 4 2024-10-20
XEPRAMYCIN 7 2024-10-21
XEPRAMYCIN 0 2024-10-22
XEPRAMYCIN 3 2024-10-23
How to solve this problem? I am using MYSQL DB
. Thank You!!!
You need to sign in to view this answers
Leave feedback about this