Liam Delahunty: Home Tips Web Contact
Recommended laptop
under £500.

Think I deserve a present? See my Amazon Wish List

MySQL - Day Of Year

DAYOFYEAR

DAYOFYEAR can be used to limit the result set so that you may compare various years data with the data so far this year.

In my oninesales e-commerce application I use the following for a snap-shot report.

SELECT
YEAR(date_ordered) as YYYY,
COUNT(oc.id) AS Orders,
SUM(price_ex_vat+vat_fee+postage_fee) as Grand,
ROUND(AVG(price_ex_vat+vat_fee),2) as AvgIncVat,
ROUND(AVG(postage_fee),2) AS AvgPost,
ROUND(AVG(price_ex_vat+vat_fee+postage_fee),2) as AvgTotalPrice
FROM orders_contact_tbl oc
WHERE
dayofyear(date_ordered) <= dayofyear(NOW())
GROUP BY YEAR(date_ordered)

Gaining results such as:

+------+--------+----------+-----------+---------+---------------+
| YYYY | Orders | Grand    | AvgIncVat | AvgPost | AvgTotalPrice |
+------+--------+----------+-----------+---------+---------------+
| 2004 |     94 |  4359.89 |     42.91 |    3.47 |         46.38 |
| 2005 |    549 | 13308.57 |     18.97 |    5.27 |         24.24 |
| 2006 |    754 | 19569.59 |     20.20 |    5.75 |         25.95 |
+------+--------+----------+-----------+---------+---------------+

Share this!