开发者

MySQL Daily Average over a month

I'm pulling several status over a specific time period and I'd like to pull another stat that would be "average sales per day" over this time period. I'm not sure how to do daily averages over a specific time period, can anyone provide some advice?

$whereSql = 'WHERE created >= '.$endTimestamp.' AND
            created <= '.$startTimestamp;

    $tru->query->run(array(
        'name' => 'get-figures',
        'sql' => 'SELECT
                SUM(price) AS total_sales,
                COUNT(id) AS total_orders,
                AVG(total) AS order_total_average
                (SELECT
                        SUM(quantity)
                    FROM `order_product`
                    INNER JOIN `order` ON (
                        `order`.id = order_product.order_id AND
                        `order`.created >= '.$endTimestamp.' AND
                        `order`.created <= '.$startTimestamp.' AND
                        `order`.type_id = '.$type->getId().'
                    )
                ) as total_units
            FROM `order`
            '.$whereSql.' AND type_id = '.$type->getId().'',
        'con开发者_运维知识库nection' => 'store'
    ));


If I understand what you want correctly, you can just divide total sales by the number of days in the selected time period. Add this column to the outer select statement: SUM(price)/DATEDIFF($endTimestamp,$startTimestamp)

Also make sure you are escaping user-supplied values with mysql_real_escape_string, otherwise you will have SQL injection vulnerabilities.


you will need to group by a to_char of the date that is scoped to the day (perhaps 'ddd') and you will need a where clause component that restricts to the proper date range

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜