SQL-计算性别的出现 [英] SQL - count occurrences of gender

问题描述

我是SQL的新手，我正在创建电影租借网站数据库.我必须显示数据库的一些摘要统计信息，因此我打算显示选择了具有特定认证的电影以供租借的男性和女性人数.

I'm new to SQL and I am creating a movie rental site database. I have to show some summary statistics of the database, so I'm planning to show the number of males and females that have selected films of a certain certification to rent.

我想要的输出是:

Certificate  No. Males  No. Females
-----------  ---------  -----------
U                #           #
PG               #           #
12               #           #
15               #           #
18               #           #

此查询的相关表为:

FILM {FID (PK), Film_Title, Certificate, Date_of_Release}
MEMBER {MID (PK), ..., Gender}
LIST {MID (FK), FID (FK)}

FILM和MEMBER表应该很容易解释，而LIST是MEMBER希望租借的电影的一部分.如果您愿意，它就像一个购物篮.每个成员只有一个列表，每个列表可以包含许多电影.我试图提出一个查询，但是Access不允许我运行它:

FILM and MEMBER table should be quite self-explanatory, while a LIST is a selection of films a MEMBER wishes to rent. It's like a shopping basket, if you like. Each member only has one list and each list can contain many films. I tried to come up with a query but Access wouldn't let me run it:

SELECT Gender, COUNT(*) AS Gender_per_Certificate
FROM (SELECT DISTINCT Film_Title, Certificate, Gender
FROM (SELECT *
FROM Film, Member, List
WHERE Film.FID=List.FID
AND Member.MID=List.MID)
WHERE Film_Title IN (SELECT Film_Title
FROM (SELECT * 
FROM Film, Member, List
WHERE Film.FID=List.FID
AND Member.MID=List.MID)
WHERE Certificate=[Certificate?]
GROUP BY Certificate))
WHERE (((Member.[Gender])="M")) OR ((("F")<>False))
GROUP BY Gender;

错误是

您试图执行不包含指定表达式'FID'作为聚合函数一部分的查询

You tried to execute a query that does not include the specified expression 'FID' as part of an aggregate function

我不确定这个错误是什么意思-有人可以解释一下吗?另外，如何正确执行查询?

I'm not sure what this error means - could someone please explain it? Also, how can I execute the query correctly?

我知道这可能是一个非常简单的查询，但我对此过于复杂了，将不胜感激，非常感谢您提前抽出宝贵的时间！

I understand this might be a really simple query that I've overcomplicated, would appreciate any help at all, thanks a lot for your time in advance!

推荐答案

如果使用case语句(访问中为IIF，谢谢@HansUp)确定哪些是男性和女性，并按证书分组，则应该您的答案.

If you use a case statement (IIF in access, thank you @HansUp) to determine which are male and female, grouping by certificate, you should get your answer.

select
  f.Certificate, 
  sum(IIF(m.[gender] = 'M',1, 0)) as [# of males],
  sum(IIF(m.[gender] = 'F',1, 0)) as [# of females]
from
  (member m 
  inner join list l on m.mid = l.mid)
  inner join film f on l.fid = f.fid
group by f.Certificate

这篇关于SQL-计算性别的出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！