SQL To List All Skus, Inventory On Hand And Qty Sold Between X And X Date

by ADMIN 74 views

Introduction

In this article, we will discuss how to write a SQL query to list all SKUs, inventory on hand, and quantity sold between specific dates in Magento 1.9. This query will be useful for administrators who need to track sales and inventory levels for their products.

Prerequisites

Before we dive into the SQL query, make sure you have the following:

  • A Magento 1.9 installation
  • Access to the database (e.g., MySQL)
  • Basic knowledge of SQL

SQL Query

The following SQL query will list all SKUs, inventory on hand, and quantity sold between specific dates in Magento 1.9:

SELECT 
  `e`.`sku` AS `sku`,
  `e`.`name` AS `name`,
  `civ`.`qty` AS `inventory_on_hand`,
  SUM(`si`.`base_row_total`) AS `total_sales`,
  SUM(`si`.`base_discount_amount`) AS `total_discounts`,
  SUM(`si`.`base_subtotal`) AS `total_subtotal`
FROM 
  `catalog_product_entity` `e`
  LEFT JOIN `cataloginventory_stock_item` `civ` ON `e`.`sku` = `civ`.`sku`
  LEFT JOIN `sales_flat_order_item` `si` ON `e`.`sku` = `si`.`product_id`
  LEFT JOIN `sales_flat_order` `so` ON `si`.`order_id` = `so`.`entity_id`
WHERE 
  `so`.`created_at` BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 
  `e`.`sku`
ORDER BY 
  `e`.`sku` ASC;

Explanation

This SQL query uses the following tables:

  • catalog_product_entity (e.g., e): contains product information, including SKU and name
  • cataloginventory_stock_item (e.g., civ): contains inventory information for each product
  • sales_flat_order_item (e.g., si): contains sales information for each order item
  • sales_flat_order (e.g., so): contains sales information for each order

The query joins these tables on the following conditions:

  • e.sku = civ.sku: joins product information with inventory information
  • e.sku = si.product_id: joins product information with sales information
  • si.order_id = so.entity_id: joins sales information with order information

The query then filters the results to only include sales between the specified dates (in this case, January 1, 2022, and December 31, 2022).

Columns

The query returns the following columns:

  • sku: the product SKU
  • name: the product name
  • inventory_on_hand: the current inventory level for the product
  • total_sales: the total sales amount for the product
  • total_discounts: the total discounts amount for the product
  • total_subtotal: the total subtotal amount for the product

Tips and Variations

  • To include disabled SKUs, remove the WHERE clause and add a LEFT JOIN to the catalog_product_entity table on the status column.
  • To include sales for a specific date range, modify the WHERE clause to use the created_at column.
  • To include sales for a specific store, add a WHERE clause to filter by store ID.

Conclusion

In this article, we discussed how to write a SQL query to list all SKUs, inventory on hand, and quantity sold between specific dates in Magento 1.9. This query will be useful for administrators who need to track sales and inventory levels for their products. By following the steps outlined in this article, you can create a custom SQL query to meet your specific needs.

Additional SQL Queries

List all SKUs with inventory on hand

SELECT 
  `e`.`sku` AS `sku`,
  `civ`.`qty` AS `inventory_on_hand`
FROM 
  `catalog_product_entity` `e`
  LEFT JOIN `cataloginventory_stock_item` `civ` ON `e`.`sku` = `civ`.`sku`
GROUP BY 
  `e`.`sku`
ORDER BY 
  `e`.`sku` ASC;

List all sales for a specific date range

SELECT 
  `si`.`order_id` AS `order_id`,
  `si`.`product_id` AS `product_id`,
  `si`.`base_row_total` AS `base_row_total`,
  `si`.`base_discount_amount` AS `base_discount_amount`,
  `si`.`base_subtotal` AS `base_subtotal`
FROM 
  `sales_flat_order_item` `si`
  JOIN `sales_flat_order` `so` ON `si`.`order_id` = `so`.`entity_id`
WHERE 
  `so`.`created_at` BETWEEN '2022-01-01' AND '2022-12-31'
ORDER BY 
  `si`.`order_id` ASC;

List all sales for a specific store

SELECT 
  `si`.`order_id` AS `order_id`,
  `si`.`product_id` AS `product_id`,
  `si`.`base_row_total` AS `base_row_total`,
  `si`.`base_discount_amount` AS `base_discount_amount`,
  `si`.`base_subtotal` AS `base_subtotal`
FROM 
  `sales_flat_order_item` `si`
  JOIN `sales_flat_order` `so` ON `si`.`order_id` = `so`.`entity_id`
WHERE 
  `so`.`store_id` = 1
ORDER BY 
  `si`.`order_id` ASC;
```<br/>
**SQL to List All SKUs, Inventory on Hand, and Qty Sold Between X and X Date: Q&A**
=====================================================================================

Introduction


In our previous article, we discussed how to write a SQL query to list all SKUs, inventory on hand, and quantity sold between specific dates in Magento 1.9. In this article, we will answer some frequently asked questions (FAQs) related to this query.


Q: What is the purpose of the LEFT JOIN in the SQL query?


A: The LEFT JOIN is used to include all products, even if they do not have any sales data. This is useful for administrators who want to see a complete list of products, including those that have not been sold.


Q: Why is the WHERE clause used to filter by date?


A: The WHERE clause is used to filter the results to only include sales between the specified dates. This is useful for administrators who want to see sales data for a specific time period.


Q: Can I modify the SQL query to include sales for a specific store?


A: Yes, you can modify the SQL query to include sales for a specific store by adding a WHERE clause to filter by store ID.


Q: How can I include disabled SKUs in the results?


A: To include disabled SKUs, you can remove the WHERE clause and add a LEFT JOIN to the catalog_product_entity table on the status column.


Q: Can I use this SQL query to list all sales for a specific product?


A: Yes, you can use this SQL query to list all sales for a specific product by adding a WHERE clause to filter by product ID.


Q: How can I modify the SQL query to include additional columns?


A: You can modify the SQL query to include additional columns by adding more columns to the SELECT statement.


Q: Can I use this SQL query to list all sales for a specific date range?


A: Yes, you can use this SQL query to list all sales for a specific date range by modifying the WHERE clause to filter by date.


Q: How can I optimize the SQL query for better performance?


A: You can optimize the SQL query for better performance by adding indexes to the tables used in the query, and by using more efficient join methods.


Q: Can I use this SQL query to list all sales for a specific store and date range?


A: Yes, you can use this SQL query to list all sales for a specific store and date range by adding a WHERE clause to filter by store ID and date.


Conclusion


In this article, we answered some frequently asked questions related to the SQL query to list all SKUs, inventory on hand, and quantity sold between specific dates in Magento 1.9. We hope this article has been helpful in providing more information and clarification on how to use this query.


Additional Q&A


Q: How can I include sales for a specific customer in the results?




A: You can include sales for a specific customer by adding a WHERE clause to filter by customer ID.


Q: Can I use this SQL query to list all sales for a specific product category?




A: Yes, you can use this SQL query to list all sales for a specific product category by adding a WHERE clause to filter by product category ID.


Q: How can I modify the SQL query to include additional columns, such as product image and description?




A: You can modify the SQL query to include additional columns by adding more columns to the SELECT statement.


Q: Can I use this SQL query to list all sales for a specific store and product category?




A: Yes, you can use this SQL query to list all sales for a specific store and product category by adding a WHERE clause to filter by store ID and product category ID.