Advanced Query Counting Dates-CodePudding

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

CodePudding user response：

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