英文:
SQL. How to add more values to a count() result?
问题
a) 表 'students' 包含学生信息，包括他们的班级/年级。
b) 表 'outreach' 包含活动信息，其中包括班级/年级以及参加活动的学生人数。
现在我需要创建一个图表（使用plotalot），显示每个班级/年级有多少学生参加了活动。
对于表 'students'，您可以使用一个简单的count()分组查询来实现。但是如何将表 'outreach' 的值添加到这些结果中呢？
例如，表 'students' 为1班级产生了45个条目。表 'outreach' 有两个属于1班级的活动，分别有36和40名参与者。如何在不运行循环的情况下得到45 + 36 + 40呢？
您可以使用在选择语句之间使用+运算符将它们全部相加，但是否有更加优雅的方法呢？
感谢您的建议！
Lori
英文:
I have this situation:
a) talbe 'students' with students that contains their class/grade
b) tale 'outreach' with event that contains a class/grade and how many students were present
Now I need to produce a graph (with plotalot) that shows how many students per class/grade were reached.
I can get there for table 'studends' with a simple count() group by query. But how can I add the values of table 'outreach' to these results?
For example, tabel 'students' produces 45 entries for class 1. Table 'outreach' has two events for class 1 with 36 and 40 particiapants each. How to I get to 45 + 36 + 40 withoug having to run some loops?
I could sum them all up using th + operator between select statements, but isn't there a more elegant method?
Thanks for your suggestions!
Lori
答案1
得分: 1
抱歉，以下是表格结构的基本信息：
学生
Bill / 班级1
Susan / 班级2
Marc / 班级3
Bob / 班级1
Ellen / 班级2
活动
活动1 / 班级1 / 35 参与者
活动2 / 班级2 / 27 参与者
活动3 / 班级1 / 31 参与者
我需要的是班级1的总数（即1 + 1 + 35 + 31），班级2的总数（1 + 1 + 27），班级3等等。
我可以使用“学生”表格的count()和group by来得到总数。但是，现在，如何将来自“活动”表格的值相加呢？
英文:
Sorry, here are the basics of the table structures:
Students
Bill / Class 1
Susan / Class 2
Marc / Class 3
Bob / Class 1
Ellen / Class 2
Events
Event 1 / Class 1 / 35 participants
Event 2 / Class 2 / 27 Participants
Event 3 / Class 1 / 31 participants
What I need is the total of class 1 (i.e. 1 + 1 + 35 + 31), class 2 (1 + 1 + 27), class 3 etc. etc.
I get the total of the 'students' table with count() and group by. But now, how to add the values from table 'events'?
答案2
得分: 0
以下是翻译好的部分：
CREATE TABLE Students
(
NAME VARCHAR2 (50),
CLASS NUMBER
);
INSERT INTO Students
VALUES ('Bill', 1);
INSERT INTO Students
VALUES ('Susan', 2);
INSERT INTO Students
VALUES ('Marc', 3);
INSERT INTO Students
VALUES ('Bob', 1);
INSERT INTO Students
VALUES ('Ellen', 2);
CREATE TABLE EVENTS
(
EVENT NUMBER,
CLASS NUMBER,
PARTICIPANTS NUMBER
);
INSERT INTO EVENTS
VALUES (1, 1, 35);
INSERT INTO EVENTS
VALUES (2, 2, 27);
INSERT INTO EVENTS
VALUES (3, 1, 31);
COMMIT;
--What I need is the total of class 1 (i.e. 1 + 1 + 35 + 31), class 2 (1 + 2 + 27), class 3 etc. etc.
--I get the total of the 'students' table with count() and group by. But now, how to add the values from table 'events'?
SELECT CLASS, SUM (TOTAL) AS TOTALIZED
FROM ( SELECT class, COUNT (1) AS TOTAL
FROM STUDENTS
GROUP BY class
UNION ALL
SELECT CLASS, SUM (participants) AS TOTAL
FROM EVENTS
GROUP BY class)
GROUP BY CLASS
ORDER BY CLASS;
英文:
CREATE TABLE Students
(
NAME VARCHAR2 (50),
CLASS NUMBER
);
INSERT INTO Students
VALUES ('Bill', 1);
INSERT INTO Students
VALUES ('Susan', 2);
INSERT INTO Students
VALUES ('Marc', 3);
INSERT INTO Students
VALUES ('Bob', 1);
INSERT INTO Students
VALUES ('Ellen', 2);
CREATE TABLE EVENTS
(
EVENT NUMBER,
CLASS NUMBER,
PARTICIPANTS NUMBER
);
INSERT INTO EVENTS
VALUES (1, 1, 35);
INSERT INTO EVENTS
VALUES (2, 2, 27);
INSERT INTO EVENTS
VALUES (3, 1, 31);
COMMIT;
--What I need is the total of class 1 (i.e. 1 + 1 + 35 + 31), class 2 (1 + 1 + 27), class 3 etc. etc.
--I get the total of the 'students' table with count() and group by. But now, how to add the values from table 'events'?
SELECT CLASS, SUM (TOTAL) AS TOTALIZED
FROM ( SELECT class, COUNT (1) AS TOTAL
FROM STUDENTS
GROUP BY class
UNION ALL
SELECT CLASS, SUM (participants) AS TOTAL
FROM EVENTS
GROUP BY class)
GROUP BY CLASS
ORDER BY CLASS;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论