Magento 2 - how to use count and group by clause in collection

Question

i have order collection and want to this query result like

`SELECT `created_at`,COUNT(`entity_id`)  FROM `sales_order_item` GROUP BY `created_at`;

i use this query

`$selectCollection=$this->ordercollection->create()->addFieldToSelect('entity_id')->addFieldToSelect('created_at');

        $querycollection=$selectCollection->getSelect('COUNT(`entity_id`)')->group('created_at');`

But not work

Just i want to get how many orders come on each date or every day ?

Answer 1

Something like this

namespace Xigen\Demo\Block\Index;

/**
 * Index class
 */
class Index extends \Magento\Framework\View\Element\Template
{

    /**
     * Constructor
     *
     * @param \Magento\Framework\View\Element\Template\Context  $context
     * @param array $data
     */
    public function __construct(
        \Magento\Framework\View\Element\Template\Context $context,
        \Magento\Framework\App\ResourceConnection $resource,
        array $data = []
    ) {
        $this->connection = $resource->getConnection();

        $this->resource = $resource;
        parent::__construct($context, $data);
    }

    public function getTestQuery()
    {
        $select = $this->connection
            ->select()
            ->from($this->resource->getTableName('sales_order_item'))
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['created_at', new \Zend_Db_Expr('COUNT(`sales_order_item`.`product_id`) as count')])
            ->group('created_at');
        ;

        //  SELECT `sales_order_item`.`created_at`, count(`sales_order_item`.`product_id`) FROM `sales_order_item` GROUP BY `created_at`
        echo (string) $select;

        $data = $this->connection->fetchAll($select);
        foreach ($data as $item) {
            // ["created_at"]=>  string(19) "2019-05-16 00:31:50"
            // ["count"]=> string(1) "2"
            var_dump($item);
            die();
        }
    }
}

If you want to do it as part of a factory collection same methods apply. Just slightly different syntax.

Magento 2: Select Field From Collection Query