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!  



Mysql Date Calulation | Mysql Day Of Year | Mysql Distinct Or Group By


 

  Tips | Home | Search | Contact

Link here: