Posted by Oli Filth on 12/29/05 03:47

svein.tjonndal@gmail.com said the following on 29/12/2005 00:01:
> I have the following query:
>
> select DATE_FORMAT(accountingdate,"%c") as month,sum(totalprice -
> freightcost - insurancecost - vat2 - vat3 - vat4) as totalprice from
> invoice where cancelled=0 and deliveryagentid=0 and (employeeid=0 or
> employeeid=37 or employeeid=53 or employeeid=50) and
> DATE_FORMAT(accountingdate,"%Y")="2005" group by month order by month;


First off, if you want to make life easier for people trying to help you
here, please use standard capitalisation for your query string so that
it's easier to read (indentation wouldn't go amiss either), e.g.:

SELECT DATE_FORMAT(accountingdate, "%c") AS month,
SUM(totalprice - freightcost - insurancecost
- vat2 - vat3 - vat4)
AS totalprice
FROM invoice
WHERE cancelled = 0 AND deliveryagentid = 0 AND
(employeeid = 0 OR employeeid = 37 OR
employeeid = 53 OR employeeid = 50) AND
DATE_FORMAT(accountingdate, "%Y") = "2005"
GROUP BY month
ORDER BY month;

is *so* much easier to read.

Please read the documentation for mysql_query() in the PHP manual:
> "The query string should not end with a semicolon."
(http://php.net/mysql_query)

Note also that using DATE_FORMAT(...) (which returns a string) as your
sort criterion actually means that it performs a string sort, and hence
sorts incorrectly - investigate the MONTH() function, which returns an
integer, and will then sort correctly.

(Note also that if you have "GROUP BY month", you don't need the "ORDER
BY month" as MySQL does it automatically).

Which brings me to another point - as MONTH is a MySQL function name, if
you have a field with the same name, you should escape it with
back-ticks, i.e. `month`. Best practice is not to use MySQL keywords as
field names!


Fix these things, and if it still doesn't work, then post again!


--
Oli

[Back to original message]