I want the latest 3 months orders-CodePudding

I want the latest 3 months of orders where I have two tables

Orders Table

CREATE TABLE `Orders` (
  `id` int NOT NULL,
  `OrderID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `shopId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `customerId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `itemCount` int NOT NULL,
  `totalPrice` decimal(13,2) NOT NULL,
  `customerEmail` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `totalWeightInGrams` decimal(13,4) NOT NULL,
  `shopDomain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

OrderItems Table

 CREATE TABLE `OrderItems` (
  `id` int NOT NULL,
  `orderId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `variantId` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `quantity` int NOT NULL,
  `sku` varchar(128) NOT NULL,
  `shopDomain` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `price` decimal(13,4) NOT NULL,
  `weightInGrams` int DEFAULT NULL,
  `discount` decimal(13,2) DEFAULT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `requiresShipping` tinyint(1) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I want to get the recent 3 months orders. actually There are two types of orders. I want to differentiate the orders by title there would be one product will be added in the orders which I need to check the orders with that products and without products. Insert demo data.


    INSERT INTO `Orders` (`id`, `OrderID`, `shopId`, `customerId`, `itemCount`, `totalPrice`, `customerEmail`, `totalWeightInGrams`, `shopDomain`, `createdAt`, `updatedAt`) VALUES
(1, '4029121691694', '1', '5376382468142', 2, '44.36', '[email protected]', '2000.0000', 'test.com', '2021-12-01 14:02:24', '2021-12-01 14:02:24'),
(7, '4029152722990', '1', '5376382468142', 2, '44.36', '[email protected]', '2000.0000', 'test2.com',  '2021-12-01 15:01:16', '2021-12-01 15:01:16'),



INSERT INTO `OrderItems` (`id`, `orderId`, `variantId`, `title`, `quantity`, `sku`, `shopDomain`, `price`, `weightInGrams`, `discount`, `currency`, `requiresShipping`, `createdAt`, `updatedAt`) VALUES
(5, '4029121691694', '32790648258606', ' product test1 ', 1, 'ST-0001', 'test.com', '19.9900', 2000, '0.00', 'INR', 1, '2021-12-01 14:02:25', '2021-12-01 14:02:25'),
(6, '4029121691694', '39639081451566', 'Static product', 1, 'STO2020', 'test.com', '0.6500', 0, '0.00', 'INR', 0, '2021-11-17 14:02:25', '2021-12-01 14:02:25'),
(7, '4029152722990', '39639081451566', 'Static product', 1, 'STO2020', 'test.com', '0.6500', 0, '0.00', 'INR', 0, '2021-12-01 15:01:17', '2021-12-01 15:01:17');

I am adding static product in the order so I need to check the all orders which have "static product" title and without static products with recent 3 month orders.

Output will be something like this, it is not exactly output for this data query but it could be something similar according to data.

"orders": {
"2021-11-01": {
  "total": 1,
  "staticProductOrder": 1
},
"2021-12-01": {
  "total": 3,
  "staticProductOrder": 2
}

}

I have execute this query

SELECT * 
FROM Orders 
INNER JOIN OrderItems on Orders.OrderId = OrderItems.orderId 
where Orders.shopDomain = '${Domain}'

I have got all the orders.

Please can someone help me in that. if you have any issue please let me know.

CodePudding user response：

Try this one

SELECT YEAR(a.createdAt), MONTH(a.createdAt), 
  COUNT(DISTINCT a.OrderID) total_orders, 
  COUNT(DISTINCT b.orderId) static_product_orders
FROM Orders a
LEFT JOIN OrderItems b ON a.OrderID = b.orderId AND b.title = 'Static product'
WHERE a.createdAt >= CURDATE() - INTERVAL 3 MONTH
AND a.shopDomain = 'test.com'
GROUP BY YEAR(a.createdAt), MONTH(a.createdAt)

Btw, your tables don't have any index as per your table scripts.

demo

CodePudding user response：

SELECT MONTH(createdAt), year(createdAt),count(*) as total
FROM Orders 
INNER JOIN OrderItems on Orders.OrderId = OrderItems.orderId 
where Orders.shopDomain = '${Domain}' AND createdAt>= DATEADD(MONTH, -3, 
GETDATE())  group by MONTH(createdAt),year(createdAt);