Mysql group by and Having clause Tutorial in Hindi आज इस आर्टिकल के माध्यम से समझेगे group by and having clause के बारे में तो group by को समझने के लिए मान लीजिए आपके पास में टेबल के डाटा है और आप यह देखना चाहते हैं जब पास में 2 टेबल है एक स्टूडेंट का डाटा है और एक में सिटी का डाटा तो आप अब यह देखना चाहते हैं कि कौन सी सिटी से कितने स्टूडेंट आ रहे हैं
जब भी हम इस तरह का रिकॉर्ड निकालते हैं तो हम यूज करते हैं group by का तो जब भी हम ग्रुपिंग करनी हो किसी डाटा की तो इसके लिए हम यूज करते हैं एग्रीगेट फंक्शन का जो कि हमने पीछे कुछ आर्टिकल में जाना था जैसे sum काउंट और यह कोई जरूरी नहीं है कि आप 2 टेबल को साथ में join करे और फिर डाटा निकाले आप किसी एक टेबल का डाटा भी निकाल सकते हैं तो GROUP by को लिखने का सिंटेक्स कुछ इस प्रकार है।
select columns FROM table_name WHERE condition GROUP BY Col_name(s);
और अगर दो table का डाटा निकलना है और फिर उसकी ग्रुपिंग करना है तो हम कुछ इस तरह का सिंटेक्स use करते है।
SELECT COL_NAME FROM TABLE1 inner join table2 on table1 . COL_NAME = TABLE2 .COL_NAME WHERE CONDITION GROUP BY COL_NAME(S);
ab aap niche me dekh sakte hai maine isme data nikala hai
SELECT *, count(course_name) from course group by course_name;
is tarike se aap condition bhi laga sakte hai iske sath me or mere ko do colum hi dikhai dete hai ek city ke id or dusra group by
select name ,count(name) from student group by name;
ab mere ko city ke name dikhe hai.
select city ,count(city) from student group by city;
ab mere ko sirf id number hi dikh rha hai lekin muje ab pata nhi chalta hai ki konsi id ki konsi city hai to iske liye mujhe dusri table ko jodna pdega.
select state . name ,count(student .city) from student inner join state
on student . city = state .id group by city;
ab mere ko aasni se samjh me aa rha hai ki ki ye konsi data hai
sabse pahle maine select likha fir maine col ka name likha jo mai dekhna chahta hu to mai state name ki table ka name col dekhna chahta hun fir maine count me likha ki studet table ke ander jo city name ka col hai uski counting karna chahta hun fir maine from likha table ka name likha jise mai add karna chahta hun fir maine inner join likhkar dusri table ka name likha jise mai jodna chahta hun fir maine on likha or stadent name ki table ke city name ke col se jodna hai state name ki id vali col se use group karna hai city col se.
ummid hai ki aap ko ye samjhh me aaya hoga. or col ka name thoda redeble nhi hai to mai iska name bhi change karta AS ka use karke.
select state . name ,count(student .city) as total_CITY from student inner join state
on student . city = state .id group by city;
ab mai iske sath me where clouse ka bhi use kar sakta hun ek baat aapko dhyan me rakhna hai ki aapko jab hi where k use kare to grop by se pahle karna hai aapko is baat ka dyan me rkahna hai.
select state . name ,count(student .city) as total_CITY from student inner join state
on student . city = state .id where state. name = “Delhi” group by city;
ab isne mere ko sirf delhi ka hi data dikhaya hai. to is tarike se aap where clouse ka use bhi kar sakte hai or aagar iske sath me aapko orderding karna hai to last me likhna hai group by ke baad me
MySQL having clause
is close ka use ham is liye karte hai mallijiye aapko paas me koi esa table hai jiska hamne upar me jaise order kiya hai or ham us osrder vale col me koi or condition lagana chahte hai. yani ki hame vo hame vo city dekhni hai jo 5 student se jyda hai. hyhan par ham where clause nhi use karte hai blki hamare paas me ek spaciel clause aata hai jise ham having clause kahte hai.
is clause ko ham jydatar group by ke sath hi use karte hai or ham ise sirf select comand ke sath hi use kar sakte hai or ise kahin bhi use nhi kar sakte hai. to aahiye samjhte hai iska likhne ka tarika kya hai.
SELECT COL_NAME FROM TABLE_NAME GROUP BY COL_NAME(S) HAVING CONDITION;
Sabse pahle apako likhna hai colum hai jise aap dekhna chahte hai or aap star ka sine bhi use kar sakte hai agar aapko puri table ka data ko dekhna hai to uske baad me table ka name fir group by fir us group ka name jise apa grouping karna chahte hai uske baad me aata hai having.
or ek baat aapko dhyan me rkahna hai ki agar aap where clause laga rhe hai to vo group by se pahle lagana hai or agar having laga rhe hai to group by ke baad me lagana hai.
jo aap candition lagate hai vo group ke base pe hi hoti hai.
select state . name ,count(student .city) as total_CITY from student inner join state
on student . city = state .id group by city having state. name = “Delhi”;
ye hi kaam ham where se bhi kar sakte hai or having se bhi kar sakte hai.
or ham having ka use ham sirf vahi use karte hai jis jagah par hame group by result nikaal kar deta hai us par ham having ka use kar skate hai is baat ka aapko dhyan me rakhna hai aap har kahi par iska use nhi kar sakte hai .
or ham count ki jagah sum min ya max function bhi use kar skate hai agar aapk koi chij samaj me nhi aayi hai to mujhe coment me jroor puchhe. Mysql group by and Having clause Tutorial in Hindi