Skip to content
Advertisement

How to find the drinker who ordered most amount of drinks in MYSQL?

So far I have been able to find the amount of drinks in a single day for EACH drinker. 

SELECT DRINKER, ODATE, COUNT(DRINK) TOTAL
FROM ORDERS 
GROUP BY ODATE, DRINKER
ORDER BY TOTAL DESC, DRINKER ASC;

I then need to insert into a sample database information about two new orders for a drinker who has ordered the most amount of drinks in one day. Here is where I am unsure of what to do exactly. Here is my attempt so far: 

INSERT INTO ORDERS VALUES(SELECT DRINKER, ODATE, COUNT(DRINK) TOTAL
FROM ORDERS 
GROUP BY ODATE, DRINKER
ORDER BY TOTAL DESC, DRINKER ASC);

I am very new to MYSQL so any help would be greatly appreciated!

Here are the CREATE and INSERT statements: 

CREATE TABLE ALLDRINKS(  /* All legal drinks */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
    CONSTRAINT DRINKNAME_PKEY PRIMARY KEY(DRINK) );

CREATE TABLE DRINKERS ( /* All drinkers */
DRINKER VARCHAR(30) NOT NULL,
    CONSTRAINT DRINKERS_PKEY PRIMARY KEY (DRINKER));

CREATE TABLE LOCATED(   /* Pubs have locations */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
STREET      VARCHAR(30) NOT NULL,   /* Street name  */
BLDG_NO     DECIMAL(4)  NOT NULL,   /* Building number  */
    CONSTRAINT LOCATED_PKEY PRIMARY KEY(PUB) );

CREATE TABLE SERVES(    /* Pubs serve drinks */
PUB         VARCHAR(30) NOT NULL,   /* Pub name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
PRICE       DECIMAL(5,2)    NOT NULL,   /* Drink price  */
    CONSTRAINT SERVES_PKEY PRIMARY KEY(PUB, DRINK),
    CONSTRAINT SERVES_FKEY1 FOREIGN KEY(PUB) 
    REFERENCES LOCATED(PUB),
    CONSTRAINT SERVES_FKEY2 FOREIGN KEY(DRINK)
    REFERENCES ALLDRINKS(DRINK)  );

CREATE TABLE LIKES( /* Drinkers like drinks */
DRINKER     VARCHAR(30) NOT NULL,   /* Drinker name */
DRINK       VARCHAR(30) NOT NULL,   /* Drink name   */
RATING      DECIMAL(1)  NOT NULL,   /* Rating of the drink  */
    CONSTRAINT LIKES_PKEY PRIMARY KEY(DRINKER, DRINK),
    CONSTRAINT LIKES_FKEY1 FOREIGN KEY(DRINK) REFERENCES ALLDRINKS(DRINK),
    CONSTRAINT LIKES_DKEY2 FOREIGN KEY(DRINKER) REFERENCES DRINKERS(DRINKER)); 

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)   );


INSERT INTO ALLDRINKS VALUES
('BEER'),
('RED WINE'),
('WHITE WINE'),
('CHAMPAGNE'),
('VODKA'),
('PORT'),
('COGNAC'),
('RUM'),
('WHISKY'),
('YABTSOK'),
('SPIRIT ROCKET');

INSERT INTO DRINKERS VALUES 
('JANUSZ'),
('PETER'),
('MARY'),
('JOHN'),
('JAMES'),
('SERGIEY'),
('CLAUDE'),
('MIKE'),
('TOM');

INSERT INTO LOCATED VALUES
('LAZY LOBSTER', 'STATION ST.', 45),
('GREASY FORK', 'VICTORIA AVE.', 345),
('CAPTAIN MOORE', 'KING ST.', 45),
('LONG JOHN', 'STATION ST.', 89),
('LITTLE PIRATE', 'OXFORD ST.', 345),
('SWEET DREAMS', 'OXFORD ST.', 267);

INSERT INTO SERVES VALUES
('LAZY LOBSTER', 'BEER', 5.00),
('LAZY LOBSTER', 'RED WINE', 7.00),
('LAZY LOBSTER', 'PORT', 8.00),
('LAZY LOBSTER', 'COGNAC', 10.20),
('LAZY LOBSTER', 'WHISKY', 6.90),
('GREASY FORK', 'BEER', 5.20),
('GREASY FORK', 'RED WINE', 7.40),
('LONG JOHN', 'BEER', 5.90),
('LONG JOHN', 'RED WINE', 8.00),
('LONG JOHN', 'WHITE WINE', 9.00),
('LONG JOHN', 'VODKA', 6.00),
('LONG JOHN', 'PORT', 9.35),
('LONG JOHN', 'COGNAC', 12.90),
('LONG JOHN', 'WHISKY', 7.15),
('LITTLE PIRATE', 'BEER', 4.50),
('LITTLE PIRATE', 'RED WINE', 7.90),
('LITTLE PIRATE', 'WHITE WINE', 5.40),
('LITTLE PIRATE', 'CHAMPAGNE', 10.90),
('LITTLE PIRATE', 'VODKA', 5.25),
('LITTLE PIRATE', 'RUM', 12.00),
('LITTLE PIRATE', 'WHISKY', 10.80),
('SWEET DREAMS', 'BEER', 3.00),
('CAPTAIN MOORE', 'BEER', 4.50),
('SWEET DREAMS', 'RUM', 4.50),
('SWEET DREAMS', 'YABTSOK', 4.50);

INSERT INTO LIKES VALUES
('TOM', 'BEER', 6),
('JANUSZ', 'VODKA', 6),
('JANUSZ', 'RUM', 5),
('JANUSZ', 'BEER', 6),
('JANUSZ', 'CHAMPAGNE', 6),
('JANUSZ', 'RED WINE', 6),
('JANUSZ', 'WHITE WINE', 5),
('JANUSZ', 'PORT', 5),
('PETER', 'CHAMPAGNE', 4),
('PETER', 'COGNAC', 3),
('PETER', 'RUM', 3),
('PETER', 'WHISKY', 6),
('MARY', 'CHAMPAGNE', 5),
('MARY', 'VODKA', 1),
('MARY', 'COGNAC', 4),
('JOHN', 'CHAMPAGNE', 4),
('JOHN', 'VODKA', 2),
('JOHN', 'RUM', 6),
('JOHN', 'WHISKY', 1),
('JOHN', 'BEER', 6),
('JAMES', 'CHAMPAGNE', 6),
('JAMES', 'COGNAC', 5),
('JAMES', 'RUM', 4),
('SERGIEY', 'VODKA', 6),
('SERGIEY', 'RUM', 6),
('SERGIEY', 'CHAMPAGNE', 3),
('CLAUDE', 'CHAMPAGNE', 6),
('CLAUDE', 'WHITE WINE', 5),
('CLAUDE', 'COGNAC', 4),
('CLAUDE', 'WHISKY', 3),
('CLAUDE', 'RED WINE', 6);

INSERT INTO ORDERS VALUES
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('16-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('17-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('18-JAN-2020', '%d-%M-%Y'), 'BEER', 4),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 2),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'PORT', 3),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('01-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('03-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'CAPTAIN MOORE', STR_TO_DATE('05-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'GREASY FORK', STR_TO_DATE('15-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('16-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1),
('JANUSZ', 'LITTLE PIRATE', STR_TO_DATE('17-FEB-2020', '%d-%M-%Y'), 'CHAMPAGNE', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('19-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('20-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('22-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('05-MAR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-APR-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('03-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('04-MAY-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('01-JUN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LAZY LOBSTER', STR_TO_DATE('02-JUN-2020', '%d-%M-%Y'), 'PORT', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('8-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('9-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('10-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('11-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('12-JUL-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('13-JUL-2020', '%d-%M-%Y'), 'BEER', 1),
('JANUSZ', 'LONG JOHN', STR_TO_DATE('14-AUG-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4),
('PETER', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 5),
('PETER', 'LONG JOHN', STR_TO_DATE('11-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('PETER', 'LAZY LOBSTER', STR_TO_DATE('09-FEB-2020', '%d-%M-%Y'), 'PORT', 1),
('PETER', 'GREASY FORK', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 2),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 3),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 4),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 5),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 6),
('PETER', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'BEER', 7),
('MARY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'CAPTAIN MOORE', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('MARY', 'LONG JOHN', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('MARY', 'GREASY FORK', STR_TO_DATE('24-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('13-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JOHN', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'RED WINE', 3),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 2),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 4),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'WHITE WINE', 5),
('JOHN', 'LONG JOHN', STR_TO_DATE('12-FEB-2020', '%d-%M-%Y'), 'RED WINE', 6),
('JOHN', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('JOHN', 'LONG JOHN', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('JOHN', 'CAPTAIN MOORE', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 1),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 2),
('JAMES', 'LAZY LOBSTER', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'COGNAC', 3),
('JAMES', 'LONG JOHN', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'LITTLE PIRATE', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'BEER', 1),
('JAMES', 'SWEET DREAMS', STR_TO_DATE('23-JUN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 2),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHISKY', 2),
('SERGIEY', 'GREASY FORK', STR_TO_DATE('14-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('04-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('06-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('23-FEB-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('03-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LAZY LOBSTER', STR_TO_DATE('09-MAR-2020', '%d-%M-%Y'), 'WHISKY', 1),
('SERGIEY', 'LITTLE PIRATE', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'VODKA', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'),'BEER', 2),
('SERGIEY', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'BEER', 3),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('10-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 2),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 3),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 4),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-JAN-2020', '%d-%M-%Y'), 'RED WINE', 2),
('CLAUDE', 'GREASY FORK', STR_TO_DATE('19-JAN-2020', '%d-%M-%Y'), 'BEER', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('04-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('05-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'LAZY LOBSTER', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1),
('CLAUDE', 'GREASY FORK', STR_TO_DATE('20-APR-2020', '%d-%M-%Y'), 'BEER', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('12-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('15-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('16-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('17-APR-2020', '%d-%M-%Y'), 'WHITE WINE', 1),
('CLAUDE', 'LONG JOHN', STR_TO_DATE('19-APR-2020', '%d-%M-%Y'), 'RED WINE', 1);

Advertisement

Answer

I didn’t quite understand your question, but I think you need a trigger to insert this data:

SELECT DRINKER, ODATE, COUNT TOTAL.
FROM ORDERS 
GROUP BY ODATE, DRINKER
ORDER BY TOTAL DESC, DRINKER ASC;

at another table. I propopse you this trigger but before all create a table for example test to collect the data:

This is the test table:

DROP TABLE IF EXISTS "test";
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `drinker` varchar(50) NOT NULL,
  `odate` date NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

Here is the trigger

DROP TRIGGER IF EXISTS "test";
DELIMIT $$
CREATE TRIGGER `test` AFTER INSERT ON `orders` FOR EACH ROW BEGIN
    INSERT into test (drinker, odate, total)
    SELECT DRINKER, ODATE, COUNT(DRINK) TOTAL FROM ORDERS;
END
$$
DELIMIT;

I don’t know if this will help you, but I’m starting too, for more information on triggers, I give you this link: https://sql.sh/cours/create-trigger

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement