MySQL使用Sum和Case [英] MySQL using Sum and Case

问题描述

我正在尝试使用ASP.NET创建一个连接到MySQL数据库的GridView.数据如下所示.

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

我当前的代码如下.这给了我错误(关于DatabaseName.sum的内容不存在.请检查 函数名称解析和解析"部分...

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 

代码继续

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

在这里最好使用Sum Case吗?

Is Sum Case the best use here?

推荐答案

该错误是由函数名和括号之间的空格引起的

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

SUM (CASE WHEN ...
   ^^

了解更多信息函数名称解析和解析

尝试

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

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于MySQL使用Sum和Case的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！