Session 33 - SQL Grouping + Sorting | DSMP 2023

-------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
| Time stamp |
-----------------------
Chapters
**************************************************************************
00:00 Live Start
6:11 Session Start
9:43 Sorting Data
12:26 1. find the top 5 Samsung phones with the biggest screen size
16:32 2. sort all the phones in descending order of the number of total cameras
19:45 3. sort data on the basis of ppi in decreasing order
22:50 4. find the phone with 2nd largest battery
29:00 5. find the name and rating of the worst rated apple phone
31:40 6. sort phones alphabetically and then on the basis of rating in desc order
34:56 7. sort phones alphabetically and then on the basis of price in ascending order

*********************************
* 35:41 -- Order By Animation *
*********************************
38:11 Doubt Clearance

10. find the phone name, price of the costliest phone
*********************************************************************************************************
39:56 Grouping Data
45:14 1. Group smartphones by brand and get the count, average price, max rating, avg screen size, and avg battery capacity
*********************************
* 53:21 -- Group By Animation *
*********************************
56:16 Group smartphones by whether they have an NFC and get the average price and rating
Avg price of 5g phones vs avg price of non 5g phones
1:00:43 Analysis of Fast Charging Available
1:01:27 Group smartphones by the extended memory available and get the average price
1:02:37 Doubt Clearance

1:04:46 GroupBY on multiple columns
1:06:41 Group smartphones by the brand and processor brand and get the count of models and the average primary camera resolution (rear)
1:11:33 Find the top 5 most costly phone brands
1:14:45 Which brand makes the smallest-screen smartphones

1:16:35 8. Group smartphones by the brand, and find the brand with the highest number of models that have both NFC and an IR blaster
1:20:18 9. Find all Samsung 5g enabled smartphones and find out the avg price for NFC and Non-NFC phones
1:23:15 Doubt Clearance
1:24:08 COUNT(*)
*************************************************************************************************************
1:27:40 Having clause
1:30:35 Costliest Brand which has at least 20 phones.
1:33:05 find the avg rating of smartphone brands that have more than 20 phones
1:34:06 Find the top 3 brands with the highest avg ram that has a refresh rate of at least 90 Hz and fast charging available and don't consider brands that have less than 10 phones
1:37:42 Find the avg price of all the phone brands with avg rating of 70 and num_phones more than 10 among all 5g enabled phones
1:40:42 Doubt Clearance
*************************************************************************************************************
1:44:47 Practice on IPL Dataset
1:45:19 - find the top 5 batsmen in IPL
1:47:42 - find the 2nd highest 6 hitters in IPL
1:49:36 - Find Virat Kohli's performance against all IPL teams [info not available- bowling team]
1:51:41 - Find the top 10 batsmen with centuries in IPL [Need sub-query]
1:58:06 - find the top 5 batsmen with the highest strike rate who have played a min of 1000 balls
2:01:54 session End Doubt Clearance

#datanalytics #Database #DBMS #SQL #DML #sqlgroupby #groupby

[JITENDRAKUMAR-xceu]

great video sir, You have explained very well. thanku so much sir.

[sukanyamanthale]

sir please please power bi sikhao , i ts request

[yashendradubey]

SIR PLEASE EK BAAR SQL KA ROADMAP BATA DIGIYE FOR DATA ANALYST AND DATA SCIENTIST K LIYE

[neerajrana]

Do we need to add ' ' around avg_ram in ORDER BY as with ' ' and without ' ' results are different. I tried on above data. In your example result is oneplus, motorola, realme. But without ' ' it gives oppo, oneplus and vivo (Video time 1:37:10)

[nikhiljagtap]

I kinda tried with sub query:

SELECT batter, COUNT(*) as "total_cen"
FROM
( SELECT batter, match_id
FROM campusx.ipl
GROUP BY batter, match_id
HAVING SUM(batsman_runs) >= 100
) as t1
GROUP BY batter
ORDER BY "total_cen" DESC
LIMIT 10 ;

@campusx, What do you guys think?

[ummeist]

# find top 10 batsman with centuries in IPL
select batter, ID, sum(batsman_run) as 'total_run'
from ipl
group by batter, ID
having total_run >= 100
order by total_run desc
limit 10;

is the query correct?

[HowTo]

Operand data type varchar(max) is invalid for avg operator. ye problem aare hai sir

[mohitujjain]

sir can you add one deploy project using render ?

[Sameer_Singh_Films]

sir are you planning for any mlops playlist?

[shardulpatel]

Sir i am unable to LOAD table fully in MYSQL only 830rows are importinng ratther than 980rows and showing error 1265 in some rows while loading data help

[capturingmylife]

sir some of the questions solution is wrong .. particularly the having statement at the end with group by .. the minimum numbers of phones one. time stamp 1:40

[CodeWithMind_]

YOU ARE ONLY THE GUY WHO IS TEACHING DATA SCIENCE IN THAT DETAILS ❤‍🔥

[sukanyamanthale]

aaap bohot accha padhate hoo, aapka bohot samjme aata he

[waqaskhiljikhan]

Sir Solve my issue i also send you mail. I am a YT Member and i can't find zoom live sessions quizs content

[LalitYadav]

1:57:27
SELECT batter,
count(batter) as 'batter_count',
ID, sum(batsman_run) as 'score' from campusx.ipl
group by batter, ID having score>=100 order by batter_count;




Sir kya ye solution ho skta h ??

[_devendra]

FOR 2ND LAST QUESTION I HAVE DONE THIS:

SELECT batter,
FLOOR(SUM(batsman_run)/100) AS "NO_OF_CENTURIES"
FROM
GROUP BY batter
ORDER BY NO_OF_CENTURIES DESC LIMIT 10