英文:
Getting the correct sex count from a View
问题
SELECT
SUM(CASE WHEN Sex = 'm' AND Teacher_id = 45 THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN Sex = 'f' THEN 1 ELSE 0 END) AS Female
英文:
I have a view results like this:
| Teacher_id | Sex | Missed_Day |
|---|---|---|
| 45 | m | 19-01-2023 |
| 37 | f | 19-01-2023 |
| 45 | m | 01-02-2023 |
| 78 | m | 01-02-2023 |
I would like a SELECT count(Sex) to give me results as follows as the Teacher_id = 45 is a duplicate so it is one male not two:
| Male | Female |
|---|---|
| 2 | 1 |
Currently I am having this :
COUNT(DISTINCT CASE WHEN Sex = 'm' THEN 1 END) AS Male,
COUNT(CASE WHEN Sex = 'f' THEN 1 END) AS Female
| Male | Female |
|---|---|
| 3 | 1 |
How do I write this query to give me the desired results by not counting sex twice of Teacher_id
答案1
得分: 3
需要首先对您的视图进行预分组：
select count(case when sex = 'm' then 1 end) AS 男性
, count(case when sex = 'f' then 1 end) AS 女性
,count(case when sex not in ('m', 'f') then 1 end) AS 其他
from (
select distinct sex, teacher_id
from yourview
) x
编辑：
如果您想将常规的计数与去重计数结合起来，您可以使用以下结构：
select COUNT(DISTINCT CASE WHEN Sex = 'm' THEN Teacher_id END) AS 男性
, COUNT(DISTINCT CASE WHEN Sex = 'f' THEN Teacher_id END) AS 女性
, COUNT(DISTINCT CASE WHEN Sex not in ('f', 'm') THEN Teacher_id END) AS 其他
, COUNT(*)
from (
VALUES (45, N'm', N'2023-01-19')
, (37, N'f', N'2023-01-19')
, (45, N'm', N'2023-02-01')
, (78, N'm', N'2023-02-01')
) t (Teacher_id,Sex,Missed_Day)
这允许保留重复项，但不会对某些字段进行计数。COUNT DISTINCT 对性能有一些影响，但对于较小的数据集，应该没有问题。
英文:
You need to pregroup your view first:
select count(case when sex = 'm' then 1 end) AS male
, count(case when sex = 'f' then 1 end) AS female
,count(case when sex not in ('m', 'f') then 1 end) AS other
from (
select distinct sex, teacher_id
from yourview
) x
EDIT:
If you want to combine regular COUNT with count distinct, you can use the following construct:
select COUNT(DISTINCT CASE WHEN Sex = 'm' THEN Teacher_id END) AS m
, COUNT(DISTINCT CASE WHEN Sex = 'f' THEN Teacher_id END) AS f
, COUNT(DISTINCT CASE WHEN Sex not in ('f', 'm') THEN Teacher_id END) AS other
, COUNT(*)
from (
VALUES (45, N'm', N'19-01-2023')
, (37, N'f', N'19-01-2023')
, (45, N'm', N'01-02-2023')
, (78, N'm', N'01-02-2023')
) t (Teacher_id,Sex,Missed_Day)
This allows keeping duplicates but not counting them for some of the fields. COUNT DISTINCT has some performance implications, but for smaller datasets, it should be fine
答案2
得分: 1
另一种方法可能是将它们作为子查询检索，类似于以下方式：
select
( select count(males.sex) as 男性
from (select sex from table1 where sex = 'm' group by Teacher_id, Sex) males
) as 男性,
( select count(females.sex) as 女性
from (select sex from table1 where sex = 'f' group by Teacher_id, Sex) females
) as 女性
这样，您可以在主查询中省略这些计数。
查看这个 dbfiddle。
英文:
another approach could be to retrieve them as subqueries, something like this
select
( select count(males.sex) as Male
from (select sex from table1 where sex = 'm' group by Teacher_id, Sex) males
) as male,
( select count(females.sex) as female
from (select sex from table1 where sex = 'f' group by Teacher_id, Sex) females
) as female
This way you can leave this counting out your main query
Look at this dbfiddle
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论