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



But not work

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

1 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
            ->columns(['created_at', new \Zend_Db_Expr('COUNT(`sales_order_item`.`product_id`) as count')])

        //  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"

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.