Q.Write the statements to print the median marks of mathematics in UT1.
🔒You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.
🔒 Start your 14-day free trial to unlock the full solution →Concept understanding — Group By Aggregation
Group By Aggregation: A First Look
Think of a pile of laundry. You have socks, shirts, towels — all mixed together. If someone asks "how many socks are there?", you first separate the socks from everything else, then count them. That act of separating first, then summarising — that is the core of Group By Aggregation.
In data, we often have rows of information about many different things mixed together. Group By Aggregation is the operation that says: "Take all these rows, sort them into meaningful groups based on some common property, and then for each group, compute a single summary value."
The Two Steps, Always
Every Group By Aggregation has exactly two parts, and they happen in order:
- Grouping — You pick a column (or columns) whose values will define the groups. All rows that share the same value in that column become one group.
- Aggregating — You pick a summary operation to apply to each group. Common operations include counting how many rows are in the group, finding the total or average of a numeric column within the group, or picking the smallest or largest value.
The result is a new table: one row per group, with the group's label and the computed summary.
Why It Matters
Without Group By, you can only summarise the entire dataset at once — one total, one average for everything. That hides almost all the interesting variation. Group By lets you ask questions like:
- For each state, what is the total number of schools?
- For each year, what was the average rainfall?
- For each product category, how many items were sold?
Each of these questions picks a grouping column (state, year, category) and an aggregation (total, average, count). The answer reveals patterns that a single overall number would bury.
A Concrete Example (Without Numbers)
Imagine a table of exam scores for students from different cities. The table has columns: Student Name, City, Score.
If you want to know "how many students appeared from each city", you:
- Group by the
Citycolumn — all rows with the same city name become one group. - Apply the count aggregation — count how many rows are in each group.
The result is a table with two columns: City and Number of Students.
If instead you want "the highest score in each city", you:
- Group by
Cityagain. - Apply the maximum aggregation to the
Scorecolumn.
The result is a table with City and Highest Score.
The grouping column and the aggregation column are often different. The grouping column defines who is in each group; the aggregation column defines what you summarise about that group. You can also aggregate the same column you grouped by — for example, counting how many rows are in each group — but that is a special case, not the general rule.
Common Aggregation Operations
These are the standard summaries you can compute for each group:
- Count — How many rows are in the group.
- Sum — Total of a numeric column across all rows in the group.
- Average — Mean of a numeric column across the group.
- Minimum — Smallest value in a numeric column within the group.
- Maximum — Largest value in a numeric column within the group.
Each of these gives a different lens on the same grouped data.
What the NCERT Textbook Says …
Select the Maths column, filter to Unit Test 1 rows, and take the median.
dfMathsUT1 = df['Maths'][df.UT == 1]
print(dfMathsUT1.median())
Output:
21.0 …
Select the Maths Series from df, filter it to the Unit Test 1 rows with a Boolean mask, then call .median() — the middle value once the four UT1 Maths marks are sorted.
dfMaths = df['Maths']
dfMathsUT1 = dfMaths[df.UT == 1]
print(dfMathsUT1)
dfMathMedian = dfMathsUT1.median()
print(dfMathMedian)
Output:
0 22
3 20
6 23 …
- CBSE 2019Set 90/1/11 markQ.Consider the following table ‘Transporter’ that stores the order details about items to be transported. Table : TRANSPORTERWrite the output for the following SQL query :
ORDERNO DRIVERNAME DRIVERGRADE ITEM TRAVELDATE DESTINATION 10012 RAM YADAV A TELEVISION 2019-04-19 MUMBAI 10014 SOMNATH SINGH FURNITURE 2019-01-12 PUNE 10016 MOHAN VERMA B WASHING MACHINE 2019-06-06 LUCKNOW 10018 RISHI SINGH A REFRIGERATOR 2019-04-07 MUMBAI 10019 RADHE MOHAN TELEVISION 2019-05-30 UDAIPUR 10020 BISHEN PRATAP B REFRIGERATOR 2019-05-02 MUMBAI 10021 RAM TELEVISION 2019-05-03 PUNE (ix) SELECT ITEM, COUNT() FROM TRANSPORTER GROUP BY ITEM HAVING COUNT() >1;›Reveal solutionSolution
This query groups orders by the item being transported and then lists only those items that appear in more than one order.
When working with databases, we often need to summarise information rather than just retrieve raw data. The
GROUP BYclause in SQL is a powerful tool for this, allowing us to aggregate rows that share common values into a set of summary rows. This particular query asks us to identify items that have been ordered multiple times, providing a count for each.Let's break down the query step by step to understand its logic:
FROM TRANSPORTER: This is the starting point. The query begins by looking at all the records in theTRANSPORTERtable.GROUP BY ITEM: This is the core of the aggregation. The database will go through theTRANSPORTERtable and collect all rows that have the same value in theITEMcolumn into a single group. For example, all rows whereITEMis 'TELEVISION' will form one group, all rows whereITEMis 'FURNITURE' will form another, and so on.SELECT ITEM, COUNT(*): After grouping, for each distinct group formed by theITEMcolumn, the query will select two pieces of information:- The
ITEMitself (which is the common value for that group). COUNT(*): This is an aggregate function that counts the total number of rows within each specific group. So, for the 'TELEVISION' group,COUNT(*)will tell us how many times 'TELEVISION' appears in the table.
- The
HAVING COUNT(*) > 1: This is a crucial filtering condition. WhileWHEREclauses filter individual rows before grouping,HAVINGclauses filter entire groups after they have been formed and aggregate functions (likeCOUNT(*)) have been calculated. In this case,HAVING COUNT(*) > 1means that only those groups where the count of items is strictly greater than 1 will be included in the final output. Groups with a count of 1 (meaning the item appeared only once) will be discarded.
NoteThe distinction between
WHEREandHAVINGis fundamental.WHEREfilters individual rows beforeGROUP BYprocesses them, whileHAVINGfilters the groups themselves afterGROUP BYhas aggregated the data. You cannot use aggregate functions directly in aWHEREclause.Let's apply this logic to the
TRANSPORTERtable:- Identify unique
ITEMvalues and group them:- TELEVISION
- FURNITURE
- WASHING MACHINE …
🎓Unlock everything free for 14 days
- ✓Full step-by-step solutions
- ✓Concept-first explanations
- ✓Methods, shortcuts & mistakes
- ✓PYQ mapping + timed mock tests
Full access for 14 days. No credit card required.