How to get order count month wise?

Question

I need to get order collection of this year where I have to display month wise order count. I am working on phtml file. I have to display how many orders in every month are placed.

Like for example Jan month had 11 order then display Jan - 11

Feb month 25 order then display Feb - 25

like this I have to display for every month. Any help would be great, Thanx in advance

Answer 1

You can do that by following below.

1) Create one block class in your extension or you can use existing block class of your extension.

app/code/Vendor/Magento/MyBlock.php

2) Define your function in Block class to load a order collection.

<?php
namespace Vendor\Magento\Block;

use Magento\Sales\Model\ResourceModel\Order\CollectionFactory as OrderCollectionFactory;

class MyBlock extends \Magento\Framework\View\Element\Template
{
    protected $_orderCollectionFactory;

    public function __construct(
        \Magento\Framework\View\Element\Template\Context $context
        OrderCollectionFactory $orderCollectionFactory
    )
    {
        $this->_orderCollectionFactory   = $orderCollectionFactory;
        parent::__construct($context);
    }

    public function getOrderCountByDateCollection($startDate, $endDate)
    {
        return $this->_orderCollectionFactory->create()->addAttributeToFilter('created_at', array('from'=>$startDate, 'to'=>$endDate));
    }
}

3)Create one phtml file in your extension (Vendor/Module/view/frontend/templates/order_count.phtml) and Bind your PHTML file with the this block class.

<referenceContainer name="content">
        <block class="Vendor\Magento\Block\MyBlock" name="show_count_order" template="Vendor_Magento::order_count.phtml" />
 </referenceContainer>

4) Call block function in your PHTML file.

<?php
$startDate = date("Y-m-d h:i:s",strtotime('2022-1-1')); // YOU CAN PASS DATE HERE AS PER YOUR REQUIREMENT
$endDate = date("Y-m-d h:i:s", strtotime('2022-1-30 23:59:00')); // YOU CAN PASS DATE HERE AS PER YOUR REQUIREMENT
$orderCollection = $block->getOrderCountByDateCollection($startDate, $endDate);
?>
<?php if(!empty($orderCollection) && count($orderCollection) > 0) : ?>
    <div>
        <span><?= __('Order Count:- ') ?></span>
        <span><?= count($orderCollection) ?></span>
    </div>
<?php endif; ?>

I hope it's will helpful for your requirement.

Answer 2

This is what I did to get things as I wanted

<?php
$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$currentYear = date("Y");
$sql = 'SELECT sub_query.month_ordered,
       COUNT(sub_query.entity_id) AS total_orders
FROM
  (SELECT so.entity_id,
          MONTH(so.created_at) AS month_ordered,
          so.increment_id
   FROM `sales_order` AS so
   WHERE year(so.created_at) = '.$currentYear.'
   GROUP BY entity_id) AS sub_query
GROUP BY sub_query.month_ordered';
$resultData = $connection->fetchAll($sql);
?>

Which gave me this https://prnt.sc/6KAQbzPH4RIh

After that I can change number into month text in PHP

Answer 3

Inspired from Klaus:

SELECT count(entity_id), MONTH(so.created_at) AS month_ordered FROM sales_order AS so WHERE year(so.created_at) = 2023 GROUP BY month_ordered;