Advanced Query Counting Dates

user_18367087 0
Viewed 21

I am trying to write a query that will return the number of orders placed during on sales-date vs on non-on sales date for each event in my Event Table.

Orders Table

OrderID (PK, INT) OrderDate (DATE) EventID (INT,FK)
1 2022-01-01 1
2 2022-01-12 1
3 2022-01-01 1
4 2022-03-10 2
5 2022-03-15 2

Event Table

EventID (PK, FK, INT) NameEvent (VARCHAR) OnSalesDate (DATE)
1 The Rolling Stones 2022-01-01
2 The Who Live in Concert 2022-03-10

The Ouput Table should be Something like

Name_Event BoughtOnSalesDate BoughtAfter
The Rolling Stones 2 1
The Who Live in Concert 1 1

My current Query that I have so far is:

SELECT OrderDate , EventName

FROM Orders

Left JOIN Event on Orders.EventID = Event.EventID

WHERE Orders.OrderDate = Event.OnSaleDate

ORDER BY OrderDate;

Which Outputs:

OrderDate EventName
2022-01-01 The Rolling Stones
2022-01-01 The Rolling Stones
2022-03-10 The Who Live in Concert
join mysql sql
1 Answers

You can Group BY the data of event to get your wanted result

CREATE TABLE Orders  (
  `OrderID` INTEGER,
  `OrderDate` VARCHAR(10),
  `EventID` INTEGER
);

INSERT INTO Orders 
  (`OrderID`, `OrderDate`, `EventID`)
VALUES
  ('1', '2022-01-01', '1'),
  ('2', '2022-01-12', '1'),
  ('3', '2022-01-01', '1'),
  ('4', '2022-03-10', '2'),
  ('5', '2022-03-15', '2');

CREATE TABLE Event  (
  `EventID` INTEGER,
  `NameEvent` VARCHAR(23),
  `OnSalesDate` VARCHAR(10)
);

INSERT INTO Event 
  (`EventID`, `NameEvent`, `OnSalesDate`) 
VALUES
  ('1', 'The Rolling Stones', '2022-01-01'),
  ('2', 'The Who Live in Concert', '2022-03-10');

SELECT `NameEvent`,`OnSalesDate`
, SUM(`OnSalesDate` = `OrderDate`) BoughtOnSalesDate
, SUM(`OnSalesDate` < `OrderDate`)  BoughtAfter
FROM Orders
Left JOIN Event on Orders.EventID = Event.EventID
GROUP  BY Orders.`EventID`, `NameEvent`,`OnSalesDate`
ORDER BY `OnSalesDate`;

NameEvent               | OnSalesDate | BoughtOnSalesDate | BoughtAfter
:---------------------- | :---------- | ----------------: | ----------:
The Rolling Stones      | 2022-01-01  |                 2 |           1
The Who Live in Concert | 2022-03-10  |                 1 |           1

db<>fiddle here

user_51935360
Related