创建一个查询来选择两列; （公司，电影数量） [英] Create a query to select two columns; (Company, No. of Films) from the database

问题描述

我已经创建了一个数据库作为大学任务的一部分，并且我在标题中遇到了问题。

更有可能我被要求找出每家公司制作了多少部电影。这通过查询向我建议了一个小组。但我不知道从哪里开始。这只是一个两个标记的问题，但语法并没有点击我的脑海。

我的模式是：

  CREATE TABLE影片
 （movieID CHAR（3），
 title CHAR（36），
年NUMBER，
公司CHAR（50），
 totalNoms NUMBER，
 awardsWON NUMBER，
 DVDPrice NUMBER（5,2），
 discountPrice NUMBER（5,2））
  

还有其他的表格，但乍一看我不认为它们与这个问题有关。

我使用sqlplus10

您需要的答案来自三个基本的SQL概念，我会一一介绍它们。如果您需要更多帮助来从这些提示中创建答案，请告诉我，并且我可以尽力引导您。

Group By

正如您所提到的，SQL提供了一个GROUP BY函数，可以帮助您。

使用GROUP BY的SQL查询看起来如下所示。 / p>

  SELECT list，fields，aggregate（value）
 FROM tablename 
  - 需要限制你的结果集
 GROUP BY列表，字段
  

GROUP BY查询只能返回在group by语句中列出的字段，或者在每个组上作用的聚合函数。

---

聚合函数

您的作业问题还需要一个称为Count的聚合函数。这用于计算返回的结果。一个如下所示的简单查询返回所有返回的记录的计数。

  SELECT Count（*）
 FROM tablename 
  

---

这两者可以合并，让您获得

  SELECT list，fields，count（*）
 FROM tablename 
 GROUP BY列表，字段
  

---

列别名

另一个答案也试图向您介绍SQL列别名，但它们不使用SQLPLUS语法。

  SELECT Count（*）as count 
 ... 
  

SQLPLUS列别名语法如下所示。

  SELECT Count（*）count
 ... 
  

I have created a database as part of university assignment and I have hit a snag with the question in the title.

More likely I am being asked to find out how many films each company has made. Which suggests to me a group by query. But I have no idea where to begin. It is only a two mark question but the syntax is not clicking in my head.

My schema is:

CREATE TABLE Movie
    (movieID CHAR(3) ,
    title CHAR(36),
    year NUMBER,
    company CHAR(50),
    totalNoms NUMBER,
    awardsWon NUMBER,
    DVDPrice NUMBER(5,2),
    discountPrice NUMBER(5,2))

There are other tables but at first glance I don't think they are relevant to this question.

I am using sqlplus10

解决方案

The answer you need comes from three basic SQL concepts, I'll step through them with you. If you need more assistance to create an answer from these hints, let me know and I can try to keep guiding you.

Group By

As you mentioned, SQL offers a GROUP BY function that can help you.

A SQL Query utilizing GROUP BY would look like the following.

SELECT list, fields, aggregate(value)
FROM tablename
--WHERE goes here, if you need to restrict your result set
GROUP BY list, fields

a GROUP BY query can only return fields listed in the group by statement, or aggregate functions acting on each group.

---

Aggregate Functions

Your homework question also needs an Aggregate function called Count. This is used to count the results returned. A simple query like the following returns the count of all records returned.

SELECT Count(*)
FROM tablename

---

The two can be combined, allowing you to get the Count of each group in the following way.

SELECT list, fields, count(*)
FROM tablename
GROUP BY list, fields

---

Column Aliases

Another answer also tried to introduce you to SQL column aliases, but they did not use SQLPLUS syntax.

SELECT Count(*) as count
...

SQLPLUS column alias syntax is shown below.

SELECT Count(*) "count"
...

这篇关于创建一个查询来选择两列; （公司，电影数量）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！