




版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
1、10/8/20221SQL(4)GROUP BY clause 10/8/20222GROUP BY clause The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns. This can best be explained by an example:10/8/20223GROUP BY c
2、lause syntax:SELECT column1, SUM(column2)FROM list-of-tablesGROUP BY column-list; 10/8/20224Example:Lets say you would like to retrieve a list of the highest paid salaries in each dept: SELECT max(salary), deptFROM employee GROUP BY dept; Example:This statement will select the maximum salary for the
3、 person in each unique department.Basically, the salary for the person who makes the most in each department will be displayed. Their salary and their department will be returned. 10/8/2022510/8/20226GROUP BY - Multiple Grouping Columns - What if?What if you ALSO want to display their lastname for t
4、he query below: SELECT max(salary), deptFROM employee GROUP BY dept; GROUP BY - Multiple Grouping Columns - What if?What youll need to do is: SELECT lastname, max(salary), deptFROM employee GROUP BY dept, lastname; This is a called multiple grouping columns. 10/8/2022710/8/20228Example:take a look a
5、t the items_ordered table.Lets say you want to group everything of quantity 1 together, everything of quantity 2 together, everything of quantity 3 together, etc.If you would like to determine what the largest cost item is for each grouped quantity (all quantity 1s, all quantity 2s, all quantity 3s,
6、 etc.), you would enter:10/8/20229Example:SELECT quantity, max(price)FROM items_orderedGROUP BY quantity; 10/8/202210Review Exercises How many people are there in each unique state in the customers table? Select the state and display the number of people in each. Hint: count is used to count rows in
7、 a column, sum works on numeric data only. 10/8/202211Review Exercises From the items_ordered table, select the item, maximum price, and minimum price for each specific item in the table. Hint: The items will need to be broken up into separate groups. 10/8/202212Review ExercisesHow many orders did e
8、ach customer make? Use the items_ordered table. Select the customerid, number of orders they made, and the sum of their orders. GROUP BY -Exercise Answers Exercise #1 SELECT state, count(state)FROM customersGROUP BY state;10/8/202213GROUP BY -Exercise Answers 10/8/202214Exercise #2 SELECT item, max(price), min(price)FROM items_orderedGROUP BY item ; 10/8/20
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 【正版授权】 ISO 19642-4:2019 EN Road vehicles - Automotive cables - Part 4: Dimensions and requirements for 30 V a.c. and 60 V d.c. single core aluminium conductor cables
- 2025至2030中国物资管理系统行业市场发展分析及竞争格局与投资发展报告
- 腹部肿瘤培训课件总结
- 多功能工培训大纲
- 白菜除虫知识培训课件
- 规范书写教案培训课件
- 实验室质量监督培训
- 调料销售培训课件
- 智慧城市规划下的公共空间设计美学与实践
- 平台在提升城市形象中的贡献
- 专题:阅读理解 30篇 中考英语高分提升之新题速递第二辑【含答案+解析】
- 企业面试题目和答案大全
- 抖音房产直播课件
- 2025至2030中国近视眼治疗仪市场竞争力剖析及企业经营形势分析报告
- 体育老师招聘试题及答案
- 自然生态探险之旅行业跨境出海项目商业计划书
- 2025年北京市高考英语试卷真题(含答案解析)
- 西藏自治区拉萨市达孜区孜县2025年七下英语期中质量检测模拟试题含答案
- 辽宁省沈阳市2023−2024学年高二下册期末考试数学试卷2附解析
- 日本所有番号分类
- 土力学与地基基础(课件)
评论
0/150
提交评论