CODEWITHSUNDEEP
mysqlsqlselect
Devression
Devression

Reputation: 457

How to find the total number of rows between dates in SQL?

I have to find the drinker and the total number of drinks ordered by each drinker in the first three months of 2020.

Here is my SELECT statement so far: (It doesn't present any errors, but doesn't return anything either)

SELECT DRINKER, COUNT(DRINK)
FROM ORDERS 
WHERE ODATE >= STR_TO_DATE('01-JAN-2020','%D-%M-%Y') 
AND ODATE <= STR_TO_DATE('31-MAR-2020','%D-%M-%Y')
GROUP BY DRINKER;

Here is the CREATE TABLE statement: 

CREATE TABLE ORDERS(    /* Drinkers visit pubs and consumes drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
ODATE       DATE        NOT NULL,   /* Order date   */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
DRINK_NO    DECIMAL(2)  NOT NULL,   /* A sequence number of a drink */
    CONSTRAINT ORDERS_PKEY PRIMARY KEY(DRINKER, PUB, ODATE, DRINK, DRINK_NO),
    CONSTRAINT ORDERS_FKEY1 FOREIGN KEY(PUB, DRINK) REFERENCES SERVES(PUB, DRINK),
    CONSTRAINT ORDERS_FKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)   );

Here is an example of the data that has been inserted into the table ORDERS: 

INSERT INTO ORDERS VALUES('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 3);

It should also be worth noting that I am working in MYSQL. Any help would be greatly appreciated!

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

You need a lower-case d for the day of the month. So this works:

SELECT DRINKER, COUNT(DRINK)
FROM ORDERS 
WHERE ODATE >= STR_TO_DATE('01-JAN-2020','%d-%M-%Y') AND
      ODATE <= STR_TO_DATE('31-MAR-2020','%d-%M-%Y')
GROUP BY DRINKER;

However, this is more sensibly written as:

SELECT DRINKER, COUNT(DRINK)
FROM ORDERS 
WHERE ODATE >= '2020-01-01'AND
      ODATE <= '2020-03-31'
GROUP BY DRINKER;

Here is a db<>fiddle.

Upvotes: 2

Related Questions