0
votes

I want to get sale stats day by day and count which section sold how much.

I'm really confused about "EVERYTHING".

$em = $this->getEntityManager()->getConnection();

$stmt = $em->prepare("
        SELECT
            COUNT(o.id) AS `all`,
            (
              SELECT COUNT(id) FROM order_panoramic_orders WHERE DAY(created_at) = DAY(NOW()) AND status_id NOT IN (7,9)
            ) AS `day`
        FROM
          order_panoramic_orders as o
        WHERE
         status_id NOT IN (7,9)
        ");

I can get daily stats but i have no idea how to deal with section seperation.

The results must be as much as like this:

Day: 1 - Total: 10 - Section1: 3, Section2: 5, Section3: 2

Any idea?

By th way;

Section and user tables are seperated.

I have to join these two tables, i guess.

First table: orders Second table: users Third table: sections

1
you need to post table schema and sample data to get help. - andrews
Desired results would also be a big help. - Gordon Linoff

1 Answers

1
votes

Strange query. Your query should just be conditional aggregation. No subquery is needed:

SELECT COUNT(*) AS `all`,
       SUM(DAY(created_at) = DAY(NOW())) as day
FROM order_panoramic_orders o
WHERE status_id NOT IN (7, 9);

Then, you can get sections using the same idea:

SELECT COUNT(*) AS `all`,
       SUM(DAY(created_at) = DAY(NOW())) as day,
       SUM(section = 1) as section1,
       SUM(section = 2) as section2,
       SUM(section = 3) as section3
FROM order_panoramic_orders o
WHERE status_id NOT IN (7, 9);