YT YouTube2Text

YouTube2Text → Transcripts → Group by in sql server - Part 11

Group by in sql server - Part 11 — Transcript

by kudvenkat · 2,863 words · 404 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I am
0:02wenet this is part 11 of SQL server in
0:05this session we'll talk about how to
0:07group Pros using Group by filtering
0:10groups and the difference between where
0:12and having
0:13Clauses now if you look at the
0:15definition the group by class is used to
0:18group a selected set of rows into a set
0:21of summary rows by the values of one or
0:23more columns or
0:24Expressions it is always used in
0:27conjunction with one or more aggregate
0:29functions now what are the different
0:30aggregate functions that are available
0:32in SQL Server there are many for example
0:35we have sum average count minimum
0:38maximum Etc let's look at them now now
0:41if you look at this table I've got the
0:42salary column and let's say I want the
0:45total salary that I'm paying within my
0:48organization so obviously what will you
0:50do if you have to do it manually you'll
0:52take each Ru and add them together but
0:55in SQL Server we have an aggregate
0:57function called sum which we can use so
1:00select sum so what do you want to sum I
1:03want to sum the salary so
1:08salary from TBL employee so all you're
1:13saying is okay total this salary for
1:16me so that gives us the total salary so
1:19we are using this aggregate function
1:21similarly if you want to say let's say
1:23for example I want to find out what is
1:24the minimum salary that I'm paying you
1:26know within my organization if you have
1:28to do it manually you'll have have to
1:30inspect each row and see okay 4,500 is
1:33less than 2ou greater than 2,800 okay I
1:35think 2,800 is the minimum salary but in
1:38SQL Server we have the aggregate
1:40function which we can use minimum okay I
1:43want the minimum salary from this table
1:46so when we execute this we should get
1:47the minimum salary and along the same
1:49lines we have the max function as well
1:52which gives us the maximum
1:58salary so max of salary so when we press
2:01that we get that okay so like this we
2:04have several aggregate functions in SQL
2:06Server so if you look at the definition
2:08you know it says Group by Clause is
2:10always used in conjunction with one or
2:13more aggregate functions okay now let's
2:16understand Group by so if you look at
2:18this table I've got a an employee table
2:21which has got their names genders salary
2:24and the city they are working in okay
2:28now I want you to write a query let's
2:30say and that query should give me the
2:32result that you see on the right hand
2:34side here I want the total salary that I
2:36paying to my employees by City okay how
2:40do we achieve this using groupby so if
2:43you look at this from a you know if you
2:45have to do it manually okay what will
2:47you do you will take each London record
2:50and then add their total salar similarly
2:52you take each New York record and add
2:55their salaries okay so you first group
2:58the records by City and then sum the
3:01salary column across that group so even
3:04the query has to do that so that's why
3:06we use Group by Cloud here so let's see
3:09how to use that so if you look at the
3:11query that we have written already we
3:13are using this sum aggregate function to
3:17Total the you know to sum up the entire
3:20salary that we are paying to our
3:22employees okay now this is the total
3:25salary of the organization but I don't
3:27want the total salary of the
3:28organization instead I want the total
3:31salaries paid by city so in our output
3:35we we not only want the total salary we
3:38also want the city okay so in our select
3:41list we need to have the
3:45city as well as the total salary now if
3:49you look at this for the total salary we
3:51didn't have a column name so let's give
3:53a column Name by using as and we will
3:56say total salary so this
4:00is an alas name for this
4:03column cool so now let's okay look at
4:07this select City sum of salary from TBL
4:11employee okay you're saying okay I want
4:13the city and the total salary but you
4:16should also tell I want to group okay by
4:22city so Group by City And then calculate
4:25the total salaries across that City okay
4:29so when I execute this
4:31query you should see the same output so
4:34the city names and the total salaries
4:37now let's see what's going to happen if
4:39I remove this group by clause and then
4:42execute this query Now by looking at
4:44itself you should you know it should
4:46make sense you
4:48know this is going to give an error
4:51because you're saying okay select City
4:53column okay I select the city column and
4:55select the sum of
4:57salaries okay so you're say you want
5:00City column and the total salary but
5:02you're not telling the query to group
5:05them by City And then total the salaries
5:08okay if I don't use the salary City
5:11column then it makes sense okay I want
5:13the total salaries in the entire table
5:16but when I use the city column you
5:18should tell it okay Group by city as
5:21well so obviously if I execute this
5:24query you will get an error stating that
5:27TBL employee. city is inv Val in the
5:30select list because it's not contained
5:33either in an aggregate function so we
5:35cannot apply an aggregate function to a
5:38text column you know what does it mean
5:40when you say sum cities you cannot sum
5:43cities London New York okay similarly
5:46you cannot find the average of city
5:48names itself okay it's only on numerical
5:51columns you can apply the aggregate
5:52functions like sum average
5:55Etc okay so we cannot use an aggregate
5:58function like sum average on this column
6:01and it's not part of group by as well
6:04that's why it's invalid in the select
6:06list okay so that error makes sense okay
6:09so the columns that you use in the
6:11select list okay they have to satisfy
6:14one of these two
6:15conditions you should either have an
6:18aggregate function applied on that
6:19column or that column should be part of
6:23group by Clause if none of these
6:26conditions are met then you get this
6:29error
6:30okay so the moment we put City in the
6:33group by Clause this condition is met
6:35and we don't get that error and that
6:37makes logical sense as well we don't
6:40have to really mug and remember that it
6:42makes sense you're asking okay give me
6:44the total salary and the city column but
6:47you're not telling it to group by City
6:49column so how will SQL Server know how
6:52it has to Total those
6:54salaries all
6:57right so obviously if you omit the group
6:59by clause and try to execute the query
7:01you get this error and we have seen this
7:03so if you look at in the output you know
7:05we are get we we are retrieving total
7:07salaries by city now let's take this one
7:10more level not only by city I want to
7:13break it down further by gender so if
7:16you look at the next slide look at this
7:17I want the output like this okay in the
7:20previous example we are we are showing
7:22the total salaries paid by city but now
7:27I want to break it down even further by
7:29by gender as well for example in Sydney
7:32how much is the total salary that I'm
7:34paying for male and female employees so
7:36if you look at the example here for male
7:38employees with and Sydney we are paying
7:402,800 for female it's 4,800 so I want
7:44this breakdown as well so we are
7:46grouping Now by multiple columns okay so
7:50we are grouping this total salary first
7:52by City And Then by
7:54gender okay so how do we do that it's
7:57pretty simple if you look at the output
7:59at s it should make sense okay in the
8:01select list we should now have three
8:03columns we already have City and total
8:06salaries columns we want the gender
8:08column as well so let's put that in the
8:10select list so I already have City I
8:13also want gender so select City Comm
8:16agenda now if I execute this query
8:19without any further modifications let's
8:21see what going what's going to happen
8:23you might you know you should have
8:24already expected we get the same error
8:27that we got before gender is invalid in
8:31the select list because you're not
8:33applying an aggregate function on top of
8:35it or and it's not part of the group by
8:40Clause so we we get the same error here
8:44so gender is invalid in the select list
8:46because it's not contained either in an
8:48aggregate function or the group by class
8:50so we need to make it part of the group
8:52by class to tell SQL Server engine all
8:55right I want you to total the salaries
8:57first by City And then by gender because
9:00we want that breakdown in that
9:03order so when we execute this query you
9:06should see the output that we have here
9:09you know it's slightly different here
9:12the cities are together for example if
9:14you look at Sydney Sydney they're next
9:15to each other New York New York they're
9:17next to each other but if you look at
9:18our output it's not so so obviously we
9:21use the order by Clause to order the
9:23result and if you're not sure on what is
9:25a by class I will strongly encourage you
9:28to watch the previous video in this
9:32series so order
9:36by city so when we execute this you know
9:40it should match the output on the
9:42presentation all right now you might be
9:45wondering okay I have by city by gender
9:49breakdown let us say I want by gender
9:52and byy City and know probably something
9:55like I want first maybe gender
10:00and then maybe City so I want first by
10:04gender and
10:09then by city in this order you just
10:12change the
10:17order and execute this
10:19query you get the same result but the
10:22columns have just switched okay first by
10:24gender and then by
10:26City cool
10:30okay now let's take this even more you
10:32know to the next step now we have seen
10:35we can group based on multiple columns
10:37now let us say if it's possible to use
10:39multiple aggregate functions absolutely
10:42you know here we are showing total
10:44salary by City And by gender let's say I
10:47want total number of employees as well
10:51now if you remember if you want to find
10:53out the total number of employees we can
10:55use let's say if you look at the TBL
10:57employee table we've got 10 rows there
10:59there and if I want to find the total
11:01number of employees within my
11:02organization I can say count of
11:05star from TBL employees when we press
11:08that we get the total count which is 10
11:11and for performance reasons instead of
11:12using star use a specific column name
11:15you know maybe
11:16ID so I get the same result okay so so
11:21what is it that we want we not only want
11:23Total salaries by City And by jenda we
11:26also want the total number of employees
11:28so
11:30if you want the total number of
11:31employees we use the count aggregate
11:33function so count of ID and if you want
11:38to give a meaningful name we will say
11:42total
11:45employees okay and when I execute
11:49this you should see we have total
11:51employees as well okay so total salary
11:55by gender by City And total number of
11:57employees and you might be wondering why
11:59did I use the square brackets here if
12:01you want to have a space in the column
12:03name then you have to use the square
12:05bracket otherwise you get an error so if
12:07I remove the bracket okay we get an
12:10error because it treats these two as two
12:12different
12:14words but you can you know if it's just
12:18one word you don't have to have a square
12:20bracket around it so if we execute this
12:22we end up with the same
12:26result all right so we have seen how to
12:29use multip multiple aggregate functions
12:31as well okay now let us see how to
12:33filter the groups okay now so far we
12:37have seen okay we got all the um result
12:41but let's say I want to see only male
12:44salaries I don't want to see female
12:46salaries so how do I filter that okay
12:50now usually to filter the rows we can
12:52use the wear Clause you know where you
12:56can say
12:58gender is equal to
13:02mail so when I execute this query what
13:05is going to happen it's going to show us
13:07only the male employees total salaries
13:10and and total number of male employees
13:12okay the other way to get the same
13:14result is instead of using the we
13:18Clause we can use having Clause but
13:21having Clause should come after the
13:23group by okay if you remember select
13:26star from table name where okay so after
13:31the from Clause we usually have the
13:32WHERE Clause but having Clause has to
13:34come after the group by Clause so now
13:37having gender is equal to male so when
13:39we execute this query we end up getting
13:41the same result now let's see what's the
13:44difference between these two queries you
13:45know here we said having where gender is
13:49equal to male and in this query we said
13:52where gender is equal to M and if we
13:54execute them together you should see we
13:57exactly have the same output but the way
13:59the query gets executed is a little
14:01different you know when you use wear
14:03Clause the T from the table only male
14:07records are retrieved and then they are
14:10grouped okay but whereas in this when
14:13you use the having
14:16Clause all the rows from the table TBL
14:18employee are retrieved they are grouped
14:21by gender and then only male groups are
14:25shown filtering female groups so here I
14:29aggregations are done for every Row in
14:31this table whereas here aggregations are
14:33not done they are they are filtered even
14:36before the aggregations are done only
14:38male set of records are retrieved from
14:40the table and then aggregated and then
14:43but remember it's possible to filter you
14:46know the output either using the wear
14:48clause or having Clause but it depends
14:50on how you know how you want the
14:57output so you can either use the wear
15:00clause or having Clause okay from a
15:03performance standpoint you cannot say
15:05that one method is less efficient than
15:07the other SQL Server Optimizer analyzes
15:10each statement and selects an efficient
15:13way of executing it and as a best
15:15practice use the syntax syntax that
15:18clearly describes the desired result try
15:21to eliminate rows that you wouldn't need
15:24as early as possible probably using the
15:26we clause
15:31okay so now we'll talk about the
15:33difference between we and having Clause
15:35now you might have already understood
15:37one difference by now where Clause
15:39filters rows before aggregations before
15:42groupings are performed whereas having
15:44Clause filters groups after the
15:46aggregations are performed okay and
15:49another difference is that you can use
15:51having Clause only with the select
15:53statement whereas we Clause can be used
15:57with other than select statements as
15:59well like insert and updates as well and
16:02aggregate functions cannot be used in
16:04the wear Clause let's see this okay what
16:07do we mean by aggregate functions cannot
16:08be used in the we clause examples of
16:11aggregate functions we already discussed
16:13count average sum minimum
16:15Etc okay so let's say select star from
16:18TBL
16:20employee where let's say sum
16:24of
16:26salary greater than 4 4,000 can I do
16:30this absolutely now I mean you can do
16:32that there is a way to do that you know
16:34using a subquery and having clause which
16:37we'll be talking about in a later
16:38session when we talk about
16:40subqueries okay but in general you know
16:43you cannot use uh aggregate functions
16:47with the we Clause whereas aggregate
16:49functions can be used in the having
16:51clause and that's what we have seen
16:53until now for example here you know when
16:56you're filtering
16:59you know you can say
17:01having maybe sum of
17:06salary greater
17:08than
17:115,000 so when we execute that we don't
17:14get the row where the sum of salary is
17:17less than U 5,000 less than or equal to
17:205,000 so you can use aggregate functions
17:22with the having Clause but you cannot do
17:24that with the wear
17:26Clause those are the differences between
17:29wear and having and this is one of the
17:30common interview question that's asked
17:32these days and it's very simple as
17:35well on this slide you can find
17:37resources for asp.net and cop interview
17:39questions that's it for today thank you
17:41for listening have a great day

About this transcript

This page contains the full transcript of Group by in sql server - Part 11 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,863 words across 404 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.