MySQL 查询问题

发布于 2024-11-04 17:49:45 字数 695 浏览 0 评论 0原文

我有当前的数据库设置：

Forename | Surname | Department | Completion_Status
Tom        Smith     Sales        Started
Bob        Jones     Sales        Completed
Alison     Baines    Sales        Not Started
Arthur     Smith     Marketing    Started
Claire     Staines   Marketing    Completed

我能够返回没有任何问题的总行数以及完成状态为“已开始”或“已完成”但不是在单个语句中的总行数 - 但是我想在单个语句中执行什么操作声明是：

COUNT for Department As Total AND the COUNT for Department As Responses WHERE Completion_Status IN ('Started', 'Completed')

它看起来像这样：

Department | Total | Responses
Sales        3       2
Marketing    2       2

希望这是有道理的！？

谢谢荷马。

原文

I have the current database setup:

Forename | Surname | Department | Completion_Status
Tom        Smith     Sales        Started
Bob        Jones     Sales        Completed
Alison     Baines    Sales        Not Started
Arthur     Smith     Marketing    Started
Claire     Staines   Marketing    Completed

I am able to return the total number of rows without any problems and the total number where completion status is either Started or Completed but not in a single statement - however what I would like to do, in a single statement, is:

COUNT for Department As Total AND the COUNT for Department As Responses WHERE Completion_Status IN ('Started', 'Completed')

It would look something like this:

Department | Total | Responses
Sales        3       2
Marketing    2       2

Hope that makes sense!?

Thanks Homer.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时常饿 2024-11-11 17:49:45

选择部门，

      COUNT（部门）    ;         AS总计，

      COUNT（completion_status）AS 响应

来自结果

WHERE  completion_status IN（ '已开始', '已完成'）

部门组

按部门订购

回复收藏 0 原文

挖个坑埋了你 2024-11-11 17:49:45

select 
department,
count(department) as total,
sum(if(completion_status  IN ('Started', 'Completed'),1,0)) as responses
from table
group by department

这是一个更标准的版本

select 
department,
count(department) as total,
sum(case when completion_status in ('Started', 'Completed') then 1 else 0 end ) as responses
from table
group by department

select 
department,
count(department) as total,
sum(if(completion_status  IN ('Started', 'Completed'),1,0)) as responses
from table
group by department

This is a more standard version

select 
department,
count(department) as total,
sum(case when completion_status in ('Started', 'Completed') then 1 else 0 end ) as responses
from table
group by department

回复收藏 0 原文

~没有更多了~