title
Group by in sql server - Part 11

description
In this video we will learn 1. Grouping rows using GROUP BY 2. Filtering Groups 3. Difference between WHERE and HAVING clause in sql server Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/group-by-part-11.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-11-group-by.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Group by in sql server - Part 11', 'heatmap': [{'end': 894.065, 'start': 872.083, 'weight': 1}], 'summary': "Tutorial on 'group by in sql server - part 11' covers the usage of group by clause, aggregate functions like sum, average, count, minimum, and maximum in sql server. it explains how to use the group by clause to retrieve total salaries paid by city and further break it down by gender, as well as adding gender to a sql query's select list, resolving errors, ordering results, using multiple aggregate functions, and filtering groups with where and having clauses.", 'chapters': [{'end': 474.093, 'segs': [{'end': 39.138, 'src': 'embed', 'start': 16.821, 'weight': 0, 'content': [{'end': 25.027, 'text': 'the GROUP BY clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions.', 'start': 16.821, 'duration': 8.206}, {'end': 29.824, 'text': 'It is always used in conjunction with one or more aggregate functions.', 'start': 26.058, 'duration': 3.766}, {'end': 34.291, 'text': 'Now, what are the different aggregate functions that are available in SQL Server? There are many.', 'start': 30.224, 'duration': 4.067}, {'end': 39.138, 'text': 'For example, we have sum, average, count, minimum, maximum, et cetera.', 'start': 34.571, 'duration': 4.567}], 'summary': 'Group by clause groups rows into summary rows using aggregate functions like sum, average, count, min, max, etc.', 'duration': 22.317, 'max_score': 16.821, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc16821.jpg'}, {'end': 219.748, 'src': 'embed', 'start': 188.699, 'weight': 1, 'content': [{'end': 189.92, 'text': "So let's see how to use that.", 'start': 188.699, 'duration': 1.221}, {'end': 193.201, 'text': 'So, if you look at the query that we have written already,', 'start': 190.42, 'duration': 2.781}, {'end': 202.766, 'text': 'we are using this sum aggregate function to sum up the entire salary that we are paying to our employees.', 'start': 193.201, 'duration': 9.565}, {'end': 209.426, 'text': "Now this is the total salary of the organization but I don't want the total salary of the organization.", 'start': 204.124, 'duration': 5.302}, {'end': 213.987, 'text': 'Instead I want the total salaries paid by city.', 'start': 209.886, 'duration': 4.101}, {'end': 219.748, 'text': 'So in our output we not only want the total salary we also want the city.', 'start': 214.307, 'duration': 5.441}], 'summary': 'Using sum aggregate function to calculate total salary by city.', 'duration': 31.049, 'max_score': 188.699, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc188699.jpg'}, {'end': 277.195, 'src': 'embed', 'start': 248.293, 'weight': 3, 'content': [{'end': 252.076, 'text': 'Select city, sum of salary from TBL employee.', 'start': 248.293, 'duration': 3.783}, {'end': 255.919, 'text': "You're saying, I want the city and the total salary.", 'start': 252.656, 'duration': 3.263}, {'end': 259, 'text': 'But you should also tell, I want to group.', 'start': 256.279, 'duration': 2.721}, {'end': 262.709, 'text': 'by city.', 'start': 261.007, 'duration': 1.702}, {'end': 268.151, 'text': 'So group by city, and then calculate the total salaries across that city.', 'start': 263.169, 'duration': 4.982}, {'end': 274.514, 'text': 'So when I execute this query, you should see the same output.', 'start': 269.792, 'duration': 4.722}, {'end': 277.195, 'text': 'So the city names and the total salaries.', 'start': 274.554, 'duration': 2.641}], 'summary': 'Query to display city names and total salaries, grouped by city.', 'duration': 28.902, 'max_score': 248.293, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc248293.jpg'}, {'end': 375.213, 'src': 'embed', 'start': 349.784, 'weight': 4, 'content': [{'end': 355.246, 'text': "OK It's only on numerical columns you can apply the aggregate functions like sum, average, et cetera.", 'start': 349.784, 'duration': 5.462}, {'end': 360.588, 'text': 'OK So we cannot use an aggregate function like sum, average on this column.', 'start': 356.307, 'duration': 4.281}, {'end': 363.929, 'text': "And it's not part of group by as well.", 'start': 361.508, 'duration': 2.421}, {'end': 365.99, 'text': "That's why it's invalid in the select list.", 'start': 363.989, 'duration': 2.001}, {'end': 368.811, 'text': 'OK So that error makes sense.', 'start': 367.11, 'duration': 1.701}, {'end': 375.213, 'text': 'OK So the columns that you use in the select list, OK, they have to satisfy one of these two conditions.', 'start': 369.331, 'duration': 5.882}], 'summary': 'Aggregate functions only work on numerical columns in select list.', 'duration': 25.429, 'max_score': 349.784, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc349784.jpg'}, {'end': 460.705, 'src': 'embed', 'start': 429.348, 'weight': 5, 'content': [{'end': 431.13, 'text': "Now let's take this one more level.", 'start': 429.348, 'duration': 1.782}, {'end': 435.874, 'text': 'Not only by city, I want to break it down further by gender.', 'start': 431.67, 'duration': 4.204}, {'end': 437.856, 'text': 'So if you look at the next slide, look at this.', 'start': 435.954, 'duration': 1.902}, {'end': 439.137, 'text': 'I want the output like this.', 'start': 438.016, 'duration': 1.121}, {'end': 445.399, 'text': 'Okay, in the previous example, we are showing the total salaries paid by city.', 'start': 439.717, 'duration': 5.682}, {'end': 450.321, 'text': 'But now, I want to break it down even further by gender as well.', 'start': 445.8, 'duration': 4.521}, {'end': 456.144, 'text': "For example, in Sydney, how much is the total salary that I'm paying for male and female employees?", 'start': 450.341, 'duration': 5.803}, {'end': 460.705, 'text': 'So if you look at the example here, for male employees within Sydney we are paying 2, 800..', 'start': 456.564, 'duration': 4.141}], 'summary': 'Break down total salaries by city and gender, e.g. 2,800 for male employees in sydney.', 'duration': 31.357, 'max_score': 429.348, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc429348.jpg'}], 'start': 0.049, 'title': 'Grouping and aggregating in sql', 'summary': 'Covers the usage of group by clause to group rows and aggregate functions like sum, average, count, minimum, and maximum in sql server, and explains how to use the group by clause in sql to retrieve the total salaries paid by city and further break it down by gender.', 'chapters': [{'end': 137.529, 'start': 0.049, 'title': 'Grouping and aggregating in sql server', 'summary': 'Covers the usage of group by clause to group rows and aggregate functions like sum, average, count, minimum, and maximum in sql server, highlighting the purpose of group by and various aggregate functions available.', 'duration': 137.48, 'highlights': ['The GROUP BY clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions, always used in conjunction with one or more aggregate functions.', 'There are various aggregate functions available in SQL Server such as sum, average, count, minimum, and maximum, which can be used to perform calculations on grouped data.', 'The aggregate function sum can be used to find the total salary paid within an organization, eliminating the need for manual calculation.', 'Similarly, the aggregate functions like minimum and maximum can be used to find the minimum and maximum salary within an organization, providing efficient data analysis.', 'Understanding the usage of GROUP BY and various aggregate functions is essential for efficient data grouping and analysis in SQL Server.']}, {'end': 474.093, 'start': 138.229, 'title': 'Using group by clause in sql', 'summary': 'Explains how to use the group by clause in sql to retrieve the total salaries paid by city and then further break it down by gender, ensuring that the select list columns satisfy the aggregate function or are part of the group by clause to avoid errors.', 'duration': 335.864, 'highlights': ['The group by clause is used to group records by city and then sum the salary column across that group, enabling the retrieval of the total salaries paid by city. The group by clause allows grouping records by city and then summing the salary column across that group, providing the total salaries paid by city.', "The importance of ensuring that the select list columns satisfy one of two conditions: having an aggregate function applied on the column or being part of the group by clause to avoid errors such as 'tblEmployee.city is invalid in the select list'. It is crucial to ensure that the select list columns satisfy one of two conditions: having an aggregate function applied on the column or being part of the group by clause to avoid errors such as 'tblEmployee.city is invalid in the select list'.", 'Further breakdown of the total salaries by city and gender, demonstrating the grouping of the total salary first by city and then by gender to retrieve the total salaries paid for male and female employees within each city. The chapter discusses the further breakdown of the total salaries by grouping first by city and then by gender, providing the total salaries paid for male and female employees within each city.']}], 'duration': 474.044, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc49.jpg', 'highlights': ['The GROUP BY clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expressions, always used in conjunction with one or more aggregate functions.', 'The aggregate function sum can be used to find the total salary paid within an organization, eliminating the need for manual calculation.', 'Understanding the usage of GROUP BY and various aggregate functions is essential for efficient data grouping and analysis in SQL Server.', 'The group by clause is used to group records by city and then sum the salary column across that group, enabling the retrieval of the total salaries paid by city.', "The importance of ensuring that the select list columns satisfy one of two conditions: having an aggregate function applied on the column or being part of the group by clause to avoid errors such as 'tblEmployee.city is invalid in the select list'.", 'Further breakdown of the total salaries by city and gender, demonstrating the grouping of the total salary first by city and then by gender to retrieve the total salaries paid for male and female employees within each city.']}, {'end': 1063.062, 'segs': [{'end': 549.615, 'src': 'embed', 'start': 476.135, 'weight': 3, 'content': [{'end': 478.196, 'text': "So how do we do that? It's pretty simple.", 'start': 476.135, 'duration': 2.061}, {'end': 481.279, 'text': 'If you look at the output itself, it should make sense.', 'start': 478.516, 'duration': 2.763}, {'end': 484.401, 'text': 'In the select list, we should now have three columns.', 'start': 481.419, 'duration': 2.982}, {'end': 487.424, 'text': 'We already have city and total salaries columns.', 'start': 484.782, 'duration': 2.642}, {'end': 489.245, 'text': 'We want the gender column as well.', 'start': 487.724, 'duration': 1.521}, {'end': 491.187, 'text': "So let's put that in the select list.", 'start': 489.525, 'duration': 1.662}, {'end': 493.288, 'text': 'So I already have city.', 'start': 491.647, 'duration': 1.641}, {'end': 495.01, 'text': 'I also want gender.', 'start': 493.829, 'duration': 1.181}, {'end': 497.357, 'text': 'So select city comma gender.', 'start': 495.936, 'duration': 1.421}, {'end': 502.781, 'text': "Now, if I execute this query without any further modifications, let's see what's going to happen.", 'start': 497.657, 'duration': 5.124}, {'end': 508.085, 'text': 'You should have already expected we get the same error that we got before.', 'start': 503.162, 'duration': 4.923}, {'end': 517.111, 'text': "Gender is invalid in the select list because you're not applying an aggregate function on top of it.", 'start': 509.006, 'duration': 8.105}, {'end': 520.554, 'text': "And it's not part of the group by class.", 'start': 518.212, 'duration': 2.342}, {'end': 523.057, 'text': 'So we get the same error here.', 'start': 520.934, 'duration': 2.123}, {'end': 530.515, 'text': "So gender is invalid in the select list because it's not contained either in an aggregate function or the group by class.", 'start': 524.251, 'duration': 6.264}, {'end': 533.096, 'text': 'So we need to make it part of the group by class.', 'start': 530.795, 'duration': 2.301}, {'end': 542.781, 'text': 'to tell SQL Server Engine all right, I want you to total the salaries first by city and then by gender, because we want that breakdown in that order.', 'start': 533.096, 'duration': 9.685}, {'end': 549.615, 'text': 'So when we execute this query, you should see the output that we have here.', 'start': 544.793, 'duration': 4.822}], 'summary': 'Add gender column to select list and include it in group by to fix error.', 'duration': 73.48, 'max_score': 476.135, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc476135.jpg'}, {'end': 593.826, 'src': 'embed', 'start': 564.242, 'weight': 1, 'content': [{'end': 571.325, 'text': "And if you're not sure on what is order by clause, I will strongly encourage you to watch the previous video in this series.", 'start': 564.242, 'duration': 7.083}, {'end': 578.262, 'text': 'So order by city.', 'start': 573.826, 'duration': 4.436}, {'end': 584.703, 'text': 'so when we execute this, you know it should match the output on the presentation all right.', 'start': 578.262, 'duration': 6.441}, {'end': 589.965, 'text': 'now you might be wondering okay, I have by city, by gender breakdown.', 'start': 584.703, 'duration': 5.262}, {'end': 593.826, 'text': 'let us say I want by gender and by city.', 'start': 589.965, 'duration': 3.861}], 'summary': 'The transcript covers using an order by clause to sort data by city and gender.', 'duration': 29.584, 'max_score': 564.242, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc564242.jpg'}, {'end': 667.993, 'src': 'embed', 'start': 634.431, 'weight': 0, 'content': [{'end': 637.473, 'text': 'Now we have seen we can group based on multiple columns.', 'start': 634.431, 'duration': 3.042}, {'end': 641.134, 'text': "Now let us say if it's possible to use multiple aggregate functions.", 'start': 637.833, 'duration': 3.301}, {'end': 646.677, 'text': 'Absolutely You know, here we are showing total salary by city and by gender.', 'start': 641.615, 'duration': 5.062}, {'end': 650.399, 'text': "Let's say I want total number of employees as well.", 'start': 647.057, 'duration': 3.342}, {'end': 658.085, 'text': "now, if you remember, if you want to find out the total number of employees we can use, let's say, if you look at this tbl employee table,", 'start': 651.059, 'duration': 7.026}, {'end': 667.993, 'text': "we've got 10 rows there and if I want to find the total number of employees within my organization, I can say count of star from tbl employees.", 'start': 658.085, 'duration': 9.908}], 'summary': 'Demonstrated grouping by multiple columns and using multiple aggregate functions, showing total salary by city and gender, with a total of 10 employees in the organization.', 'duration': 33.562, 'max_score': 634.431, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc634431.jpg'}, {'end': 902.018, 'src': 'heatmap', 'start': 872.083, 'weight': 1, 'content': [{'end': 874.304, 'text': 'Whereas here, aggregations are not done.', 'start': 872.083, 'duration': 2.221}, {'end': 877.925, 'text': 'They are filtered even before the aggregations are done.', 'start': 875.064, 'duration': 2.861}, {'end': 882.247, 'text': 'Only male set of records are retrieved from the table and then aggregated.', 'start': 878.305, 'duration': 3.942}, {'end': 894.065, 'text': "But remember, it's possible to filter the output either using the where clause or having clause, but it depends on how how you want the output.", 'start': 883.548, 'duration': 10.517}, {'end': 902.018, 'text': 'So you can either use the where clause or having clause.', 'start': 898.775, 'duration': 3.243}], 'summary': 'Only male records are aggregated and filtered.', 'duration': 29.935, 'max_score': 872.083, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc872083.jpg'}, {'end': 961.454, 'src': 'embed', 'start': 930.959, 'weight': 2, 'content': [{'end': 935.563, 'text': "OK, so now we'll talk about the difference between WHERE and HAVING clause.", 'start': 930.959, 'duration': 4.604}, {'end': 938.506, 'text': 'Now, you might have already understood one difference by now.', 'start': 935.623, 'duration': 2.883}, {'end': 944.291, 'text': 'WHERE clause filters rows before aggregations before groupings are performed,', 'start': 939.067, 'duration': 5.224}, {'end': 948.315, 'text': 'whereas HAVING clause filters groups after the aggregations are performed.', 'start': 944.291, 'duration': 4.024}, {'end': 954.461, 'text': 'And another difference is that you can use HAVING clause only with the SELECT statement.', 'start': 949.436, 'duration': 5.025}, {'end': 961.454, 'text': 'Whereas WHERE clause can be used with other than SELECT statements as well, like INSERT and UPDATES as well.', 'start': 954.932, 'duration': 6.522}], 'summary': 'Where filters rows before aggregation; having filters groups after aggregation.', 'duration': 30.495, 'max_score': 930.959, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc930959.jpg'}], 'start': 476.135, 'title': 'Sql query and aggregate functions', 'summary': "Explains adding gender to a sql query's select list, resolving errors, ordering results, using multiple aggregate functions, and filtering groups with where and having clauses. it also covers the inability to use aggregate functions in the where clause.", 'chapters': [{'end': 549.615, 'start': 476.135, 'title': 'Adding gender to sql query', 'summary': "Explains adding the gender column to a sql query's select list, encountering an error due to missing aggregation or group by clause, and resolving it by including gender in the group by clause resulting in the desired output.", 'duration': 73.48, 'highlights': ["The chapter explains adding the gender column to a SQL query's select list.", 'Encountering an error due to missing aggregation or group by clause.', 'Resolving the error by including gender in the group by clause resulting in the desired output.']}, {'end': 1063.062, 'start': 549.635, 'title': 'Sql aggregate functions & filtering', 'summary': 'Covers using the order by clause to order results, grouping based on multiple columns, using multiple aggregate functions to obtain total salary and number of employees, filtering groups with the where and having clauses, and the differences between them in terms of performance and usage, with a brief mention of the inability to use aggregate functions in the where clause.', 'duration': 513.427, 'highlights': ['Using the order by clause to order the result by city or gender breakdown. The speaker encourages watching the previous video on the order by clause and demonstrates how to use it to order the result by city or gender breakdown.', 'Using multiple aggregate functions to obtain total salary and number of employees. The tutorial explains how to use multiple aggregate functions to obtain total salary and number of employees by using the count aggregate function and providing a meaningful name for the total number of employees.', 'Explaining the differences between WHERE and HAVING clauses in terms of filtering groups and performance. The tutorial delves into the differences between the WHERE and HAVING clauses, emphasizing their impact on the query execution and the ability to use aggregate functions.']}], 'duration': 586.927, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FKSSOpQe5Jc/pics/FKSSOpQe5Jc476135.jpg', 'highlights': ['The tutorial explains how to use multiple aggregate functions to obtain total salary and number of employees.', 'Using the order by clause to order the result by city or gender breakdown.', 'Explaining the differences between WHERE and HAVING clauses in terms of filtering groups and performance.', 'Resolving the error by including gender in the group by clause resulting in the desired output.', "The chapter explains adding the gender column to a SQL query's select list.", 'Encountering an error due to missing aggregation or group by clause.']}], 'highlights': ['The aggregate function sum can be used to find the total salary paid within an organization, eliminating the need for manual calculation.', 'The group by clause is used to group records by city and then sum the salary column across that group, enabling the retrieval of the total salaries paid by city.', 'The tutorial explains how to use multiple aggregate functions to obtain total salary and number of employees.', 'Understanding the usage of GROUP BY and various aggregate functions is essential for efficient data grouping and analysis in SQL Server.', 'Further breakdown of the total salaries by city and gender, demonstrating the grouping of the total salary first by city and then by gender to retrieve the total salaries paid for male and female employees within each city.', 'Using the order by clause to order the result by city or gender breakdown.', 'Explaining the differences between WHERE and HAVING clauses in terms of filtering groups and performance.', 'Resolving the error by including gender in the group by clause resulting in the desired output.', "The chapter explains adding the gender column to a SQL query's select list.", "The importance of ensuring that the select list columns satisfy one of two conditions: having an aggregate function applied on the column or being part of the group by clause to avoid errors such as 'tblEmployee.city is invalid in the select list'."]}