MySQL using Sum and Case

Question

I'm trying to create a GridView with ASP.NET connecting to a MySQL database. The data appears like below.

BusinessUnit    OrderDate      Canceled UnitA           1/15/2013          N UnitA           10/1/2013          N UnitB           10/15/2013         N UnitB           10/22/2013         N UnitB           10/22/2013         N 

Based on the records above, I'd like the result to appear like below

BusinessUnit  TodaysOrders   ThisMonthsOrders  ThisYearsOrders UnitA              0                1                2 UnitB              2                3                3 

My current code is below. It's giving me error (something about DatabaseName.sum does not exist. Check the Function Name Parsing and Resolution' section... )

Select       SUM (CASE WHEN (OrderDate)=DATE(NOW()) THEN 1 ELSE 0 END) AS TodaysOrders,     SUM (CASE WHEN YEAR(OrderDate) = YEAR(CURDATE()) AND MONTH(OrderDate) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS ThisMonthsOrders,     SUM (CASE WHEN YEAR(main_order_managers.creation_date) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS ThisYearsOrders  

code continues

FROM OrderTable WHERE OrderTable.Canceled. <> 'Y'; 

Is Sum Case the best use here?

Answer 1

The error is caused by the space between function name and parenthesis

SUM (CASE WHEN ...    ^^ 

Read more Function Name Parsing and Resolution

Try

SELECT BusinessUnit,        SUM(CASE WHEN OrderDate = CURDATE() THEN 1 ELSE 0 END) TodaysOrders,        SUM(CASE WHEN DATE_FORMAT(OrderDate, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') THEN 1 ELSE 0 END) ThisMonthsOrders,        SUM(CASE WHEN YEAR(OrderDate) = YEAR(CURDATE()) THEN 1 ELSE 0 END) ThisYearsOrders   FROM OrderTable  WHERE Canceled <> 'Y'  GROUP BY BusinessUnit 

Here is SQLFiddle demo