सबसे पहेले एक टेबल बना लेते है और उसमे डेटा इन्सर्ट कर लेते है.
तो ये हमारा टेबल इस तरह से बनेगा.
SQL में बहुत से Aggregate function इनबिल्ट है
जिनमे प्रमुख रूप से उपयोग में लिये जाने वाले Functions के बारेमे आज हम चर्चा करेंगे.
सभी Employee की Salary का टोटल प्राप्त करने के लिये आप Sum() Function का उपयोग कर सकते है.
देखिए यहाँ Colum Grid में Column हेडिंग (No column name) है. यानी की कोलम का कोई नाम नहीं है.
अगर आप column को कोई नाम (Alias Name) देना चाहे तो इस तरह से as की-वर्ड लगाकर दे सकते हो.
दुसरे Aggregate Function देखते है जो की एकदम आसान है.
यहाँ पर हमने Salary Column में से सबसे कम salary, सबसे ज्यादा salary, एवरेज salary एवं Employee की संख्या प्राप्त की है.
अगर हमें City वाइज Salary चाहिए तो Query इस तरह से Group by close के साथ होगी.
इसी Result Set में आप salary को सबसे कम से अधिक के क्रमिक रूप में देखने के लिये Order By क्लोज जोड़ दे.
अगर आप City wise और State wise Total salary चाहते है तो Query इस तरह से होगी.
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfAAAAFTCAIAAABJRsnxAAAgAElEQVR4nO2de4/cxqHl65MEyAcIsLpzx2hzP0H+36wTBLHXM7r1ETbrGBfQDZwgHo0BLmBbmMBeaIHYSDa2NKNdMAniKFolF0mkjh3rMfKalmPHbz3nqRnJMrh/8FXFerCKLDbZxfNDQeoh2ew67K7T1UWyDkkAAAB4Aem7AgAAANyQGfru7u7u7u7Jkye3trbSf21JEpKXJEmSY8eO9ScKAADGCAx9nJC5LQAAJTB0X+ndeeH4AMyaGRs6mqVbenfPARYAxstgDR3tM+nbGee0ADBehm/o82sHvWsZZwFgvGQNAIaO4ksBYLxkDWDghh6HJCUIe/eLGZWIklwziUNCI25VL8eheBfCuFwYBnk9KaHB7CpDlZ8HS4L8eTRJgiRJkoTmS8IkCfPHxWZx/sQoX85z/sSJ8zeta5ES50czCOP6rRtw8/yJE+c72bMBubpMXFx+dAoc625wPJnPM6VB6Hz/lkTUcv9ZA3Bi6MfUMC/XylDaG1kYkGjmVmhbIkoILVUTwhl6X7rE488uiSghjKHb1qdB/RWfBxtSy06hjDtTxriTfHmcJCRJqGz7gs3Xj72+aVcHgTgMOjMIJxVsSEVX8WdECY2SJElMdIdBum3z1zXcMqKE1Bm67f6bYbX/rAG4MnTpa0gNvfwmDEhQGEFMym9tobsnacDq7dkebhiQMOY3JkYu2U+JSUDlq+S/VFS68uVphzp7botOtN7Q6+uTkCRiO2MG29t+HtoYOovK0KnsMcP5E1W7ZD7nQVAYRPnplHS+JA1YsX268yCM864ijZKi38h3hIuXtuqkRwkhWamYWxjkq4IkEL/YZKiMqTB0fv/Mx6F4fvVzwuzC6nha1rDP92suDF3Vs8uctzCvoKYBq7YXe7jFZnPQQ49qfohIjUyui99VS+2q15Uarqo+EbMB+4Uq3d7285AkxPBzX1KMpbCupDH0KEnC/EHVyKrdX2WPL4oKLwqDqp3JDEK9feod6Z4jmq2KKO8j7DM2Xz9mPCgUJczrJsVe4jBhvpsS4trQw4DxwYiy7ibvodseTzXM55n5Buvz/ZoHQ0/yMdCUrCULPbVylbQBq7fXDOaOy9DZ5XW7rS36Ia+K4crrw79lNYZu+3nISlNCgyEXmn+KA2Z5wU2xg8501dh2zXc26w1Csz3vBaWawhSEDcRaKomTgJSddPZ1KbPccAzE1NDjqmmzCuSGbns8zWpbenqv71fPhi6OnteMoUelEejPqokNWLX9fBt6zIxBGRurUlfu4+2F157DoITrgIsvRwnX467todt+HpKEGH7uta2BN/RYMPSifdYbOkPZ04wo0wEV7EtswNrtFQZRLBcd0NzQKUmY101kVpokkeseurWh2x5PQ4p69fx+zUEPnf0pnUTl8Ij++g2xAau2ryynTGewfOmo2uMbTtHUX2VkGl1hQCKxe75Jjh0jx163qJXkC5W94oUf+pfUJ66eLKkYulh/289DkpBk8/Vjx46ZOhbhjbs44RnKHrMbpNQNuXAtsfCHOAz4rl+NQei3VxlEag2StcZDLjEzOB4nAdNDD4OE0ZUQKjxXuj+LIRd2w4hyQy75qoiWY9BWx1MJZ8SlE/f8fs2Joat+R0tXUVIlqtsVu5w7+RlJNh5gkdZfcxx0umRfXZuvk2PHyPmbbQ2dhb2cUVqf4qQuIYRS/q1R1F/zUZEa+ubrx46ZjxFXns22muLiRfYSl2Iz9k+GSueXr3zZrplf5AGlQb4uEt7f7CmG21e7doXlsdicFI1DZryFlqMu5RnRdkMuypEJ7ZBF++OpoLI918vu7f2aC0NHmWmRjZ6fP0FOnLfbj5PLRh0WaX3OnzC38w7o8apAEVl3bziXLYIqis57n4be6XXoKLaFPyXHrLpJThwjm5Z7k95Y1FeR31h0k5if8OuINjcWuUJxKnZYNxaBAuX71e+NRXlJEtz6P5YywPcUgPGSNQAY+ogLAMATsvYMQ/e6AABGQdbaB5BY1LvrzXsBAIyd4Ri6qnooMGsAgBEDN3QAAACmwNABAMAT+jT0iMrn/QFSIqq8IFU60b4kQMBsfmfD/QMAhkZ/hh6bTqM8TqSzyulvGZPeWs3dv9zU0FX7BwAMin4Mne2bp3Pkp6RzRARhOYkEOx0zN3dEOrFnkE3jGUV200oMGvVE/mEQhFGxVjJnp8LQmemN0p0HQTaTSnYnGo2yiUeCMCyTQWr3n+iCCGQT9rPS2G8X1XIAgA3D66FHjMVH5WRAZTMvpussZoCLMitXzvA5hyh66IzPxtWp83WTHwnuSaNyMqBiXrkwqPhyzf6VQQSKCfsrPxiK7VXLAQBWDNHQq805TgJ+fs6IJmHM7CGfwHMMhs4urUy1bNRDT5ixl3zeUtbQLfZvOG91sVQyqM9GcEl/kwAALIChDxSTMXRjQ+fRGbrN/rWGLpuwP1LEqKuWAwDsmAdDr0yonyS0GHLx2dCFifzTIRFuNLx+yEUy9KHtoWvyG2VDLsoggkQ2YT+fIJDULgcAWNGPoVcmyC8ST9igQu5aRjHbsFhCszH04lSqJwgT+aeXLabz5fMnLeUT7UtGMgiNiqU0Ki6EjPOx8zBg90/YrxT5kIg2a1E6Yb8q80G1HABgDm4sAiWOr0tUp20BALoAhg4ymNistiPa6gn7AQAdAkOfa0YyBdi81x+AGQFDHzJu/brr0rtYAMYODH0g9G7HHhQAxs7MDB3NUkXvPuhNAWDsZM1gSIZu22jn3SN690FvCgBjJ2sG82PoKCiqAsDYyZoBDB1l/gsAYydrBrM3dOaqZxJREsa9GUF60XSPFdDUKj0+cUhoxK0Kwh6qFIdZjdhjVb6PlNBgdpWh2RXz7EIAxk7WDGZs6KwlpTbRr5+GwbAMPaKEUO74sIaukhB1XKs4rH6RsEsiSghj6Lb1aVB/oT4AjJ2sGczU0GMSKLpyeRZC2RmM+FXsQtbp0v5s2rzTLcOw3L7WrMOAhBEJKtvH2ZL0z6xKM+iExiSg8lXFYeGMLCaVSVsy93ddf72h19cnIUlULiyfqNmeXSXUHIYOQIU+DD3SjhhETOuNsrYdBsxTorJtc2MRzG5TT48YU9D3/rLvgFjYnq/qDHrB9cdHYaPyujmtv+p1pYarqg/7Dc3+7JBuz/5yisPqS8DQAaiQNYO+DL2c84NK1mZF6LEWY+4aQ2fNojIGLRbN9qXR1Pmss+LQ0J3WX94fZ9bWD7nwnfEaQxd67oT/YoahA1AhawazHnJRGLTcdxoZOjvMYmLoyu2jciRnFt3zRDckpTFWZfXc1V9v6ElCKD9EJr4cJVyPu7aHrj/LCkMHoELWDGZ8UrRioDWGLmxPA6FJxyQg/JCLzQk6bnthiCYMSDSz7nl+QNiXo6T6A0J3iKJqT1Ze/01y7Bg59rpFrcTXZQ268r0rqQ/7RRWTQOihi/XXX88DQwegQtYMZn3ZYuXXdG4ElSAF+ckxxfL0LGjRG6XMvvRenJ5QZbevnkQVLHIGhT0JXOgVgiaYWkWyher6b75Ojh0j52+2NXQW7rjJ6hMzZ6rTAy49X1r5NpLrhaEDINCToXfvhi4vQ5xt99x9kdX//Aly4rzdfmqHXGZcYOgAVMiagU+GrrruokHhgxr6tzBn9b9JThwjm5Z7k95Y1FfJbyxCIhIAJST9zydDH19x9UmYlwIAkJM1Dxj64MuQGYlMAIZO1oRmFXDRuy3ORQEAgCbM2NBVdUCBoQMA2jIEQx8aMG4AwFwCQwcAAE+AoQMAgCfMn6FTkpDA5Q4jmhCSDPCC5ji7oj672DoOxbmqHF+HXbyE+X6ZgAtKg9D5/i2JGl+f7vxzBcDsmT9DT5KEum54YTBQQ2eNqfgzooRGSZIk/Ho5YZBu2/x1DbeMKCF1hm67/2Y03r/zzxUAM6Y3Qw+DhJCsRPzCIEzikF8VJ0G+MY2YhscsZ7tXyv2oKxNG5a4yN8h3nneQq6/SNSpjKgydhem+5yuFLn35LOYWUvElmhl6tYpW+1dsnweexLkUGpW6+B8uzHeJhaFbfq4S9nMbJAGzKv2dly4fZv8AjIF+DD0MkrIBRnybiZhWFCWpB1FmSIT9acy2nDis34+mPhUfj/KdsJ3OMKj5YnCLuaGHAeODEWXdTd5DjyLmS7S6N6seLjPRAnOkbPev2T4Po0ofZ6siyvs+9wzz+tt+ruKw/DxEtFwe0YRQZvtBjuCBMdChoVcoXzNOAspVIqJMA+A9VLIkzntGbDdK6OxL9qMmDDjHj8Pyz9LEbXboBFNDj6umzdqd3ND5znsbQ+d2WRwg2/1rtue9u6DUJWxgWv9GnysqXYixGjAM+uihuzJ0fUOyNHTWAVhDL/Yz4+550qGhR5Tp0AvPbjwGXdTLdv/a7RWGXiwXBbY1dEODZn5ZwtDBQOhtyIVtcZQfKhGNmDK9ocpPXaVrWxo64cdPI35tNPPueWI35MJuGFFuyCVfFdFyDJrvSjc1dM6ISye23b9+e5Whp1YuW2s15GL1ueI+t1E5zFLZnpKaIT4AOqKnk6L8r9ri00/537lsN7lcTqvDneJPY+V+ZKSnsygtt6+6QVR/WrULRGNSjkxohyzExczmAaVBvi4SAjTqjhy3MdfLttq/4fbV2hRfUXXHTa3A7nMlXShuDzcHfTGXly3Omj6650n3l/fNPYrOO44bGC0wdB0R7bPbVbmxCBTwwR2SNThkYJzA0AEAwBNg6AAA4AkwdAAA8AQYOgAAeAIMHQAAPAGGDgAAniA39EMAAADzg87Q3zfmes6HH374hz/84ZVXXnn++eeff/75FxjEJQAAAByiM/R/2PPJJ5+88sorH3/88d7e3i4AAIAZojP0z4z5NOfGjRsvvvji/v7+7u7e7u7uzk5Ztrd3trd32CUoKCgoKA6LztC/4Llx40b64M6dO9vb23fu3PlC4Pbt2ydOnNjb29/d3d3e3m5wWhUAIGVnZ3dvb39vb1/6oO/agUFgbeipm3/rW9/a3t5WGfru7t7W1vadO3dv3bqNgoLipOzs7P7HIKD/8i+VBz/8t3/b2dntvXooQyh2hp66+de//vXvf//7WkPfvXN369at2zdu3ERBQXFStrd3lpeW/ufJk5UH66dPb2/v9F49lCEUC0NP3fxrX/vaN7/5zZdffllj6Ds7u7dv37lx4+bnn3+BgoLipGxtbf23739/Y2Oj8uDs2bNbW1u9Vw9lCMXC0Hd2dr7xjW8sLi4+9dRTp06d0hj6F1/c+OCDD999973NzXdQUFCclI8//iT18cqDs2fPfvzxJ71XD2UIxbqH/t3vfnd1dfVXv/qVxtA///yL69f/fu3a/7t06SoKCoqT8uGHH6U+Xnlw9uzZDz/8qPfqoQyhNBlD//GPf/z73/9eY+ifffb5u+++d/ny5ptvvo2CguKkvP/+B/89DH/3u99VHvzpT396//0Peq8eyhBKw6tcoijSGPqnn3527dq7b7116cKFv6KgoDgp7733/vrp0xcuXKg8ePvtt9977/3eq4cyhNLJdej/+OjjN998+9///S9//OOfUFBQnJS33rr0zjvxO+/E0ge9Vw9lCKWJoWtIDf29994/d+6PZ8/+3zd+dw4FBQUFZTZFZ+i3jLmZc/fu3V/+8pcXLlz8298u//Wvf5tO30RBQUFBmU3RGfodnrt3796pY2tr66233jp16tT/AAAAMFt0hn4DAADA/KAzdAAAAHOE0tABAADMFzB0AADwBBg6AAB4AgwdAAA8QWnoz66+4Gvp+5gDAEAn6Aw98REYOgDAV1KXG5ehj6f0/ekCAMyU1OXGZegPxwEMHYCxkbqcnaHTtam0LNBqmYVDW/Ls6gs7POfOnTt37twZnpMnf7Yzz8DQARghqctZG/raNMnKWkIVZV4MfW2tWs8bN27QtbVejNgVMHQARkjqYG176GLffIFOFxYUhh6HAeGgUSNvjsMgCGPLJz27+sI2T2Ho29vb01vJ9Fby6oUtSte255kvv/wShg7A2EitzM7Q2S7txYsXi8fnzmWz8R4eHq6tTRcWqJERN/Ll8omWTxdtLpVz7dq16XR68eLFVy9sra1NKV3r4+1wBgwdgBGSulxzQy9MPEmSM2fOJElyeHi4vb29tjZ99NFHdUZc/h3RZn30poZ+l2dtbbo2nZ45c2ZtbXry5M/SQunaXUPe/PEiIeS//C/u78Ufv2n6/E6AoQMwQlKXszP0kyd/du3atYsXL57jST0xPaNoYejFn+VQTLa6WBCEMfes0sopDcpncNurDf02jziG/uqFrayrPp2uTafXr1+/rWH6o8XFxcXFH02LPwkhxZ898eDBAxg6AGOjoaEXj9ke+traNB3And5KTp78mc7QJUPojGFnD/OeexwGhEZyQ2d76Pz2akOvxC2trU0/+uijdDbh6TR59cLWqxe2WFELC/T69evKuKYLzywuPvPM44vPXLh169atWz9/fPHxxxfzv/oChg7ACGli6NIhl8PDwzNnzmzn5xVrDL3sQufuW3X51Kr57nqNofPbqw39Js/a2vT69evc74zpNNWZrNG1tenFixdTT78p5S/PLC4+85efP774zF9u3rz588cXn/n5M4vpH395ZjGTk/5dLqisz59b6n/85ze5J4jPUewzBYYOwAhpYugvvvjidDpN7a8YfU7/LYah19bWTIdc0o61ulsdUUKMeuj89mpDv84jDrlkhp4kSZJcv359gdKFBZp6uoSz//pP//SvZ9N/r7/8nfJx9l+5zfWXv0O+83L6J/nOy9U/hX2yK0z3mXHv3j0YOgBjI3Uta0MvvW9tmp4F3d7ePnnyZ9NbSTpq8eKLL5pe5SI6ejHGQorB9SCMmXOnESWioVe3Vxp6zJMaevrb4saNG9NbuaGv0YTSJEno2sLa2nSB0ljKG08fOfL0G/EbTx/59ksvfZt8+6VsyRtPH+F+c5SLjjz9Rv5M9s9iAbN5sc50nxkwdABGSFtDT4dZinHzYgzaxtALK4+o5qQod9I0YKw8fRY3bKM/KfouT2HoqZtPb3FjStvb29evX1+bThcW6LtSfvv0kSNP/zb9/8iRx17Kl/z26SPksZdkz3jpMUKYVfmfLz1Gjjz9W2af+Z65JWb7hKEDMEIaGnpxTQs7zJJe3PJijtLQe+XZ1Rfe4Untu3Dz6TQ3dEoTuvDRRx/RtYXU0N+R8psfHDnyg9+kD8hjPy2XFH/nq37zgyOk2PTID37K//mbnz6Wb/7OTx8j4h5M9vmbrFL7+/swdADGRupy1ob+6KOPLizQyr/sg5TO7dmeZ1df2OThDH2a0OlCcRnPjRs3rl27du7cufRal00pv37qyJGnfi1fsvaf87GR9O9fP3WE/Vv1p3yR2T5TYOgAjJDUuMY12+IVnvQ6lvT2orRkhk4XErpw8eLFBbqQGvqVHjnxLfKtE+ab7+3twdABGBupy43L0C/xpCc8n3jiiQVK05IaenZjUT6mtLBAL82aF/9T2WP/D//1/1g8E4YOwAhJXW5chv52Hal9i6X2icNhd3cXhg7A2EhdblyG/tYIgKEDMEJSlxuXoaOgoKB4WVKXG5GhAwCA39QY+i9f/98oKCgoKAMvpobe6ffJzs5Op/ufDX6osMJjyR5LA94DQ3eAHyqs8Fiyx9KA9/Rs6Nvb253ufzb4ocIKjyV7LA14DwzdAX6osMJjyR5LA94zRENnEy8aRY7OGrkFMDLmQoUVouRiwkzS7M2Tznxcewwb54yrgaGD+aVnQy+uhy8o5zZPktQlaNgkEnqWiCryiduTJJEF6VUYsDQVMskp2uBvu4MgHEOrHTZFLQ2AodOzod+9e5dfoG6gA3Y9QYWstn4ZukRyhjtDNzksHRw6tTQAhk69oX/55cODwwddlIcPv/rs8y+4/V99bjJ57kply6vPTSZHlyeEEEImzx1fJmT5TLpqfZlMVjc7ql5zFYcPDg43j0/Yum0ez+t/5fDB+nI5LLG8wa06uPrcJFvz6PGrfeqyl5yWM8vk6Dr73mVyjq7rDoL0ra8cw+wdlz5Ltjz/zNh8WlJpPTVGANpSb+gPvnx47+B+F+Xhw68++fQzbv9XVieT1cuVLdOFxaorqxNydP3g/r2DjaXsQZ9FoiIrmyuZmU1WrphJO9hcmUxWrqgPxTCKWnLlTdlcmZCl9fv3Du5fPj4hyxtmB0F9DE2eVSwvDrvNpyWV1lNjBKAtfRr6V1999fEnn1ob+sH99WWytH7/3vpRsrzRu7tJVFTK+lFSsZ4rq5OiP1n9rmIRXGwYRSuZ8c2KZMODoDmGhoeuxaclldZTYwSgLQaG/uDL/XuHXZSHX3310cef8vu/ujKZrFzmt7y8OpmsXkr/TZesL5OljdNLwpZ9FJmKStlYIsuny/pvLJG85hVpl1cnZPl034raSd5YKiSwb1n1TVQfBM0xVD5LuzebT0sqrafGCEBbhmboh5dWJqRs2BtLhCytiA1+Y4kQomv/PbvbpZUJWdrYLwyl6mW55VVXXV2ZkKX13JiGau6mhs7IyQ6I0UFQH0Pls/R7s/i0wNDBXFNv6PcffLm3f9BFefjwqw//8bG4/0sr5a/npdMHe5eOPzI5fml/fYkQQpZP7R/s7R+cWiKPrFzpqGJOVJxaKs/Sndo/2GPqzwokVWnr+fMmz17qX52V5L2s/tl7tLd/sHfp+COlTO1ByN5l/TFUPku/N/NPSyqtp8YIQFvqDf3w/oPdvXtdlIcPv/rgw48a7X/9SbJ0qptazVDFvJZ5k2zxaUml9dQYAWiLgaEfPtjZ3e+iPHz48O8f/MN6/6eWCCGPPHupo1rNSMU8l3mSbPlpSaX11BgBaEu9oR8c3t/e2euifPnw4ft//7C7/c+m+KECkllpPTVGANpSb+hXARgZPTVGANpicqdohzx48KDT/c8GP1RY4bHkBw8e9NQYAWhLvaF3HVZNCPHgX+ANPbVEABxgZOjdvfzW1tbnt3fmvRBCujtEw8RX4/NVFxgJ/Rs6IaR3R25ZRugCvkr2VRcYCf0beu923L6gh+4NvuoCI6F/Q0cPfR7xVbKvusBIaGPo2igDM5Q99OlP/plwfO8Xakud/uSfF3/y52H20NlktnlLsdCgMb5CcfHhqCxhIwaLxeVxyp8mLpkBMHQw1zQz9KJJOjB0eQ+94tEqy06Xmxh6l6avdIGIcoeIiVSrHk5br+875EgnOatY/n0vLmGJw4BGjJz8kbhkJsDQwVwz4B4657+vfY88+Wobs+7S0BU99C6daLCGXmKUKRfRIIy5j1L2UFwyE2DoYK4xMnSjsLFGxbaH/uoT/CBMtYf+2vcIIenjctDmyVdvv/XDRUKKVbPpoas8l10ec/nX7PAMjdK1lAaEEEq5VfkvpP48vd74xFhn6ZJKd7x6SNitZgEMHcw1RoZuEDbWsBiPoQvdc9bKM0N/7XvkkR9O0w3e+uFiNuz+5x89Qp54rYceOutDhVVLvUrVl43DQPRs1VNmi9740i8c1rzFJUk+3JLA0AFwRP+GbtBDL92ZM3rW0NMlT7wmebr5OHvXPXSVVxVL2NOF4paqVX1gNuSi76KXQykwdACcYGbo9dk0DYvpGPovniRPvPY52wev9NDzcZXS92do6KZj6DWGzpwzrW6pWdUPJsanH0Tn1mIMHQAX9G/oRmPo2UlR5tToL54k1SGXtP+ebjDTIRezq1wimo16817FGbp0eVK1twEbOtMJzx6KS/LHjABc5QKAC4wM3ShsrFExvsolM+g//+gRIh1yyTf+848eIWkvvhiHYc+Xai6Vcd9DT5KEPdEZhFFpVvmygO1ucxdo865dXZXtd4ZXaPOYXIcuPYNQbsTXHdehA9AeI0PvLmys7Z2ifd9SVNNDN6Tv7nYDfDU+X3WBkWBm6J2FjbWay+UXT3InQvsruh56LT13tRviq/H5qguMBCND7y5sDHO5zCm+SvZVFxgJRobeXdYXZlucU3w1Pl91gZFgeKdoV6CHPqf4KtlXXWAk9B9Bt9V3epyrf4Ef9NQSAXBA//Ohd7fzmeGHCit8leyrLjASYOgO8EOFFb5K9lUXGAkwdAf4ocIKXyX7qguMBBi6A/xQYYWvkn3VBUZCQ0N3dVu2sv1Up1uNw0Ax/bfJbZYd34pZo8LVHexDuqFUY3xiBF2+uFxgcqM/bv0HwJYmhq6YbqkJOitM5zkpX7K1oXdmiFoV9pPAmiRj9I1SsiRwTggsNJmKC5NzAWBP2yGXlq1Nb4Uhzfcd0YDS5jbXYw9dMx+VLXNh6CWV6srnw1VOlovpcwGwp00E3YODw83jE7K80SqCTl6v1A2yxMkkouVUhcw4Bo2Swvq5X+eyLLeh9NCldePqX59I1//kL/XGV/3xZhdngYALABrQJrHo/uXjEzJZvdwusUher0ryQ/m49Almvm2ZVYj76cXQS09Wh8lV6q9LpOO190ft7zbNGDoMHYCOaG7o7d283tDT1MnUCqyavjSwrd8eOrtQrJtUUU0iXZ+Objbk0jxwDoYOQAMaJhZdWpmQyeqldnFFaWKRvF7subEgkA+bKJu+IrBtEIauDZOr/C6p3bIDLYaYGB9fR4yhA9A5jQx9fZm0Dp8zNPSyn6cZcpEYuiywbSiGrg6Tqxq6sKWrq4tao5KsriMjB1e5ANANTSLoTi0RlkdWrrSJoJPXSzWOnDADEcWfQr+1OnY9IEPX5cxVeuKSLdUD8jPG5Dp0vo64Dh2Azuk/gq4n4S5xrKLv4RQT/HjjRHzVBUZC/xF0PQl3iUsVc5JI58cbJ+KrLjAS+o+g60m4S/xQYYWvkn3VBUZC/xF0PQl3iR8qrPBVsq+6wEjoP4KuJ+Eu8UOFFb5K9lUXGAmDiKADYDj01BIBcADmQ3eAHyqs8FWyr7rASIChO8APFVb4KtlXXWAkwNAd4IcKK3yV7KsuMBJg6A7wQ98NGTYAABPNSURBVIUVvkr2VRcYCW0j6FreAaNoPxGt7Fk6d0mDOyq7uQlT5wLspOaDv//THI1kzU38xVuIW/8B6IJGhi6JGWuIqv1UjFfuw8M39MoXEzN/Ytta9T09gOaNq07OJX5aMDkXAN3QcsilbWNT7pzbseJVBuODChVdOtFQDZ1FOumv6WS5mD4XAHvaRdBdfW5Cjq43zZ/TRdCxjpU/lAfLCdMvVjdLhKQI1wdRrkLluerpFdWZc5Ryq3I9/Xm6gfGJPfQ4DGoiPTRLutFRBYYO5pp2iUWELK13k1jET5dbbc/SecPFcXZhCnXloEc76g2dnVJW716V5ZrgumH30Nm65+qzoG8YOgAd0SpT9N7B5sqklafr2k9px0xXne1oM37N9Vblm7H7dIxpD73WvfQ1V63qA73xSb+JktpID+0St/VXAUMHc03DCDo2i26yctV9YlGSZA05KlqzNpIt6wbSqI/kNtMx9Br30tS8Rvvs0f+0UvwMYgZhMIYOQAc0MfRLKxOytLF/73D/3tWVCVla7yCCLkmSPFCU+eGuCJbjsouEzSqpdb1d5RLR7HeEKoVOk06nDa7rA9MLe9iRsGIVrnIBoBuaRNCxKXRt8ud0EXQpvAHHivC2WMik4zZjFzHfDw4xuw69/LVR1jCtjyBEHFcRRPUchKGSzJ7XJcJpalyHDkCnIILOAW1V9N3dboAfb5yIr7rASEAEnQNaqZiTzLkKfrxxIr7qAiMBEXQO8EOFFb5K9lUXGAmIoHOAHyqs8FWyr7rASEAEnQP8UGGFr5J91QVGAiLoAODoqSUC4ADMh+4AP1RY4atkX3WBkQBDd4AfKqzwVbKvusBIgKE7wA8VVvgq2VddYCTA0B3ghworfJXsqy4wEloZevvJaGvaz5zcQqlUwd2w7+7+oQEcFv2nQqqW/bTg1n8AuqCFocdh0DpioaGhD8DRWHSHiK1nSzm6GeJnjUoyM/c8Pz89+2nB5FwAdENjQ8/SdNBDT8wNveVEsPNg6Cz83GLMpwXT5wLQDU0j6DaOkuUzV1YfnaxuNs6f00bQJUlSFz4XFwltRJiXVdymQ0+w7aGrsvTE8DxmIsmojJ2LwyAIQ34KQ+k+Q2Gaw84lc+KZoCgaSb+PEHABgEOaJRZtLJGj6wf3Lx+fTI5vdhRBlyR1oT/M5LqZeUiNofvkTeMxdMFXVWkVlRnSU3l8sFE5sKE6RJptWlNrfMyBz1TA0AHomiaGXvj47Aw9lgWwVRo9a3mJ2is7wKyHXhleFuSIyyvpehpH1OyzWo2OJRf6+IQlffVh6AA4oUFi0dWVCdftdB9BV236igA2W2PoDNMhl3J4WCZHXM48j+jHLGYevKcxvlidSkJkkU0YQwfAFa0yRVsGiqoj6HJ7yv7XhM9VrqjoJ6rN8qSoTQQdm64X6Q1dLXyWhi5E0BXIrlvBVS4AuKRhBF1aLq1Muoqgy87xMUPk8iGXgEufU6e7dYqxoZdD/RI5svA85qRocdJUNrKkja+bpaFLI+jEWuA6dAC6YJ4j6GbaddPR1gUGI8QcX43PV11gJMxzBN1gfLCVCkTQDQlfdYGRgAg6B/ihwgpfJfuqC4wERNA5wA8VVvgq2VddYCQggs4BfqiwwlfJvuoCIwERdABw9NQSAXAA5kN3gB8qrPBVsq+6wEiAoTvADxVW+CrZV11gJMDQHeCHCit8leyrLjASYOgO8EOFFb5K9lUXGAnNDJ29wbvVvT269sPczD7w2250KpwdqmFRZ3zchFr8BAbZatz6D4BzGhu6m0ambj/89IG2Lzfbm0iVKiozVakyWBvUtu+7ZNVvXOHeufDy7cv1Y3IuALqhkaG7a2LK9tPyJQZh6F1WYriGnsJ85TNVtZgsF9PnAmBPowi6q88VM6J3FkEXV6OG5HOdZ/FylAtgyzuJs7I8uQqV56onbZfFyA1CoIjNkEshuBpdlFQPgG5JJzIEYOhgrmkUQbd+lExWLx/cv3ewuTIhS+sdJRbxQ6/SRi9a2gxzLQrqDb2wasMUjoEJFLEaQ1clLyUwdACc0ixTVBJH10kEXYoqsaGyhD33ODRDZ5dohGhU9CpQpNGQS/YIhg5AR7RKLGofWmTWfvjQ+ERs9NoAtu4xHUOvcS+Nip4Fipgbem28EsbQAXBFE0O/tDIhSxv79w73720skcnKZecRdPyFLfoguuzvHpLnCsyucokokUVqcoY+UIEiFj308ssof1dxlQsA3dAwgu7UUvbrv6sIOu4MIRMcmo05VOPlhPC2mcZGmF2HXoaCqoRoYuT6FShiNYbe7KpzXIcOgC3zHEE3GNqq6Lu73QA/3jgRX3WBkTDPEXSDoZUKRNANCV91gZGACDoH+KHCCl8l+6oLjARE0DnADxVW+CrZV11gJCCCzgF+qLDCV8m+6gIjARF0AHD01BIBcADmQ3eAHyqs8FWyr7rASIChO8APFVb4KtlXXWAkwNAd4IcKK3yV7KsuMBJg6A7wQ4UVvkr2VRcYCU0Nvbgvu90dMcr2w93qXvcize60nE1Mh7mKWoZ0Q2md8bG3/ktS+HDrPwBd0DxTlE8Va4jOCmunT2Vnne3I0M323EqF+YvOh6ELEXTibImYnAuAbmhk6C1dnMHIClXzp86XobefBXY+DD1FHkEnrsT0uQA4pEkE3ZXVR8nymTbJc/URdLK+baSKYYvDIAhDfhBIFufGb8NNTpsPBpSDJDRKYsOktxYqdBVTJ9L1P/mLnaHzAy4IuACgI5okFl0+PpksH81iRZc3GscV6RKLhOliq2uFoB9JnkJl48o2YqwEMw179qh9D91Ehbxiui25+eL7wMLQhfnQYegAdERDQ+88U1TaiFUxbNLWr9o44fyR6+zqfUWNGxXii+q37NXRbU6KlpjbNwwdgAY0iqBbXy5i57qKoJM0YnUMm6T1azPbSkPPxlWUaWnuDd2sYoYS6irWHW0MHWPoAHREs0zRjSWyfPre4f69qysTsrTuPoJOYYWKGDa5oasz2yQd4epIhrMhF3MV1YrJtmTDm+ZkyIWpav4VhatcAOiGhhF0e6eXs6GApfVOIuhkrVgYkc6zIWRd1+rG2t53HAaE8E9jz5fWeWcrFeqKSbZUD8jPGKseenF2t6g1rkMHoAsQQecAxyr6Hk4xwY83TsRXXWAkIILOAS5VzEkinR9vnIivusBIQASdA/xQYYWvkn3VBUYCIugc4IcKK3yV7KsuMBIQQecAP1RY4atkX3WBkYAIOgA4emqJADgA86E7wA8VVvgq2VddYCTA0B3ghworfJXsqy4wEmDoDvBDhRW+SvZVFxgJMHQH+KHCCl8l+6oLjIQmhs7dkt7uNhhl+6ncLWl482RP91iaqSjnF+iE2WqvMz7JhFrsPMW49R+ALmjdQ4/DoEV7c2boNpMjOsdERZna1waNwKEYujgrcblYmG4Lk3MB4JK2hh7RVo1tJD30OAzc9M3nwNBTKj30OA1bKmc8w/S5AHRAkwi6slx9bjJ57krXEXTMn7pUtryHbpBFlz7FmUvUqOA753oJ2srnnV/loagm6lW3cYedoUeUnRMTARcAdESTxCI2uqhNXJFxBF3pYtwG2YyyzC/02DCLzrE/mKjQ5fcwM/fWV16suyoWQ9ymmTwZNoaePYahA9A1bQx9Y4kcXW/h5hYRdFwYBWPxFZOQ2oDqKe7QGnreVWYrppFgXnlxlfjV0dkU6uaGLuqDoQPQEc0SixyEz9klFondz0q7V9qANsjNEQYqZNYulWBaedmqSqKeuM0MJCfFS5fRT9UfWhhDB6AbGhv61ZXJZOVyKzdvZOjaFDq5J6oj3xxhpCKzdFdBetr9lCPwEm/vVnJSvF7VhGXXreAqFwBc0iKCrl34nF0EHXu5iOWQi/Ip7jBUkQ6Sh3oJ+soz+RcaXXF+iaDw9O4lZ2gNHdehA9ANiKBzgB8qrPBVsq+6wEhABJ0D/FBhha+SfdUFRgIi6BzghworfJXsqy4wEhBB5wA/VFjhq2RfdYGRgAg6B/ihwgpfJfuqC4wERNABwNFTSwTAAZgP3QF+qLDCV8m+6gIjAYbuAD9UWOGrZF91gZEAQ3eAHyqs8FWyr7rASIChO8APFVb4KtlXXWAkNDT04p7ylveT69qPfu7ZIaFUoZozcv6pMz7u1v+IKu/qLz5BuPUfgPY0MnRu+qdWFqVuP/xMgYZt2twxnXorDJ1BmMa3fCcLlxdmesHkXAC4oKmh502s5dSmplboHBh6O6x66DmiWUs2x/S5ADSmWQTd5vHJo8evPjg4PLNMjq53EUEnjeEsB2FolGS+wGW2lXMu5qFuuRVostzaY2vouhQ6vl8rzZmrydibCU0MvfilxQymmUdeuFYgB4YO5pqmiUVXVieEEDJZudJNYlGSCAP15cgLk+XA5+KUUQ9MrkTFWDqIudAZOuGpvGilwtKpzKtb1sXUORKlx9bQ0yNRfrvy7w8MHQAnNDL0K6uTyerlg/v3DjZXJq083aj9iMEUqkYvXS70Cp3Pim495KKuTHXAQVNt1d4cidLTdMil/usVhg5AY5okFrHhcy2D6MzaDx8an1gaetB5EJ2loWsrE1FCo4imf2u3VO3NkSg9jQxd/aMCY+gAuKBRBN36MpmsXsqC6MjSuvsIOq4vl1mWbMhFZejMsITEHvo3dE1lIspdy6feUrU3R6L0mBu6+G4wS/Ia4yoXAFzQMILu1FL+G79dEJ2m/TDn+nLDEgfVlT30oHKdPDeazWe5tcd2yKVaGX4z1pZ1W6r25kJRLc2uQ2d/h6iW4Dp0ABrjYwTdzK8OdKpilgMMzfHV+HzVBUaCjxF082vo7HDLsPHV+HzVBUYCIugc4IcKK3yV7KsuMBIQQecAP1RY4atkX3WBkYAIOgf4ocIKXyX7qguMBETQAcDRU0sEwAGYD90BfqiwwlfJvuoCIwGG7gA/VFjhq2RfdYGRAEN3gB8qrPBVsq+6wEiAoTvADxVW+CrZV11gJDQ0dCFUrCHK9sPdHBSHgex+m8HkRehUcMdIISQZkBZDNMZXmaAhMbutv5cb/UVg6GCuaWToklCxhpgYekQVjXwwJqhVEQSVmVlmYOjdHxntdxgfT2gy8VZPU3GJwNDBXNPE0CszRHUaQceEVSi36R29ipCWX00BpfI6+2ToqrndrZb0BAwdzDWNIug2jpLJc1eyLDoyWd10H0GX+gLfOVfmsdnHtrmlVkU+u3kQRpoUurpsubhIqqNUiJ1jBjqiMmBP+5ROJJeOnjmz+Tz2lef3AgwdzDUNI+jWl1NnmCwtTybHN90nFjFzwiqDEqqznzeKbXNBjaEXE5dL52E3qWS5jVD7yqzo6dHgwoxUT2mFzviy904yhgJDB6BTmmaKMs7emaHnXVnp7OCMDTSJbXNK3fhDHAY0SqvHWW1dJfXbsKtUJqp7SqeSOfEwdABmQ6PEorJ0lVjE+4Jg7RUbsI5tc4zJmYAgCJgBiEbZcuU2yti5iBJS6aHXPcWtZLkOjKEDMBMaZoqSpY0si44sn27q5maGXli6Jo/NNrbNMfUqinEhqckpDV2jV1jFxjhFUkN3mVSnlFzuPNeMq1wAmBVtI+iWTjfPn9NF0PHtOh1hDtV5bLaxbW4xVMEuMamkZpvqqurV33nAnvYpnUhuetU5rkMHoD1+RND1HNs2QhfwVbKvusBImP8IugHEto3QBXyV7KsuMBIQQecAP1RY4atkX3WBkYAIOgf4ocIKXyX7qguMBETQOcAPFVb4KtlXXWAkIIIOAI6eWiIADsB86A7wQ4UVvkr2VRcYCTB0B/ihwgpfJfuqC4wEGLoD/FBhha+SfdUFRgIM3QF+qLDCV8m+6gIjoY2hc/dnNrt1W7lztzeqd4zhIWp7Ryt3TGx2ZDjhgc28CBrj4ychSBLc+g/ArGhm6EWbzRtf08mVtIbO71yclXAw2Bm6dG5bExpPSjNLQ+empzeeiguTcwHgAkc99KbTnxoZet9TtdTSg6FbHZMZGzozYxoi6ACYJY0i6LJyZpkcXT98cHD44Mrqo0UQHfu4VQSdpocey1LlyhGJYqLaLH2ta3+wMfQgyPLh0rqRctbbytyJYuXlwRDqhDkx0aLyKpXhEelRtZbMz1KPxCIAZkibxKKNJXJ0/eD+vYP7l4+XQXTsYycRdIT95V78Ww1sK78BskflFOGd07qHzs9yG6jT5sRjokyYY74Ss8EP4VXE4DrjrD6d8cWIoAOgH4Zs6PyQi2ok3WRJx7Q1dP5sZ5bzLFZePbt6UtmJ6oBUXiWus94GkoVvKRg6ADOjTQTdxlIRV7S+nGUY3Ts8vWQRSmdm6HGZwjNnhs5XolIxztBl8dbanfFLDDLtxFfJqQbXqSpgIFmyD4yhAzArHBn65dXJZPVSljI6Wbns1NCLAQGNoUuHXPo3dOHSzkpONDOUzQx8895aUGPolYQ5/oBUBmHKMRamKpEDQ0cEHQA90jCCbm//YG9/fYksnxJC6cjSupsIOmZ0oPQFzU907gTfTJ1B6wKMktJ8U03Fg2Ihf8JA3JN6yEUcYGcOSBBwL607KdrW0BFBB0B/zFsEXc8dODkjdAFfJfuqC4yEuYqgY3u0Q2KELuCrZF91gZGACDoH+KHCCl8l+6oLjARE0DnADxVW+CrZV11gJCCCzgF+qLDCV8m+6gIjARF0AJT01AwBcEO9ofdUMQAAAHbA0AEAwBNg6AAA4AkwdAAA8AQYOgAAeAIMHQAAPIH0XQEAAABugKEDAIAn/H/NxzXBQOBHRQAAAABJRU5ErkJgggA=)
Create table tblEmployee ( ID int primary key identity(1,1), Name nvarchar(50), [State] nvarchar(20), City nvarchar(30), Salary int ) insert into tblEmployee(Name, [state], City, Salary) Values('Prakash', 'Panjab', 'Pathankot', 15000), ('Farhan', 'Gujarat', 'Valsad', 5000), ('Sandip', 'Karnataka', 'Harihar', 6000), ('Heena', 'Gujarat', 'Rajkot', 8500), ('Mayuri', 'Haryana', 'Sonipat', 7000), ('Pradip', 'Gujarat', 'Surat', 8500), ('Umang', 'Gujarat', 'Surat', 9500), ('Nirali', 'Gujarat', 'Valsad', 8000), ('Kavya', 'Haryana', 'Sonipat', 6500), ('Gaurang', 'Maharastra', 'Kalyan', 8000), ('Hitesh', 'Utter Pradesh', 'Bijnor', 8000), ('Manoj', 'Rajasthan', 'Jaipur', 10000), ('Usha', 'Gujarat', 'Rajkot', 11200), ('Ali', 'Karnataka', 'Harihar', 8000), ('Lalit', 'Rajasthan', 'Jaipur', 8000), ('OM', 'Gujarat', 'Valsad', 16000), ('Pravin', 'Panjab', 'Mohali', 13000), ('Rina', 'Gujarat', 'Rajkot', 14000), ('Vimal', 'Rajasthan', 'Jaipur', 6000), ('Hemali', 'Panjab', 'Mohali', 18000) Select * from tblEmployee
तो ये हमारा टेबल इस तरह से बनेगा.
SQL में बहुत से Aggregate function इनबिल्ट है
जिनमे प्रमुख रूप से उपयोग में लिये जाने वाले Functions के बारेमे आज हम चर्चा करेंगे.
सभी Employee की Salary का टोटल प्राप्त करने के लिये आप Sum() Function का उपयोग कर सकते है.
देखिए यहाँ Colum Grid में Column हेडिंग (No column name) है. यानी की कोलम का कोई नाम नहीं है.
अगर आप column को कोई नाम (Alias Name) देना चाहे तो इस तरह से as की-वर्ड लगाकर दे सकते हो.
दुसरे Aggregate Function देखते है जो की एकदम आसान है.
यहाँ पर हमने Salary Column में से सबसे कम salary, सबसे ज्यादा salary, एवरेज salary एवं Employee की संख्या प्राप्त की है.
अगर हमें City वाइज Salary चाहिए तो Query इस तरह से Group by close के साथ होगी.
इसी Result Set में आप salary को सबसे कम से अधिक के क्रमिक रूप में देखने के लिये Order By क्लोज जोड़ दे.
अगर आप City wise और State wise Total salary चाहते है तो Query इस तरह से होगी.
Group
by क्लोज का उपयोग करने के लिये इन बातो का ध्यान रखे.
Select list में जो Column Aggregate नहीं है वह
सारे कॉलम Group by के अन्दर भी होने चाहिए.
इस
उदहारण में हमने राज्य वाइज़ कितने Employee है और Sate वाइज़ कितनी Salary है यह
परिणाम प्राप्त किया है.
अब
यह Query Run करने पर Total Employe count और सभी Employee की Total Salary
मिलेगी.
ऊपर
वाले दोनों Result Set को मिलाकर एक रिजल्ट सेट चाहिए तो Union All से दोनों Query
को जोड़ दे. और परिणाम को A to Z छोटेसे बड़े क्रम के चाहिए तो लास्ट में Order by
क्लोज का उपयोग करे.
Union All से एक रिजल्ट सेट प्राप्त करने के लिये दोनों
क्वेरी में Column की संख्या बराबर होनी चाहिए और Column के DataType मेच होने
चाहिए.
Group
by के साथ Where और Having close भी Use किया जा सकता है.
Where क्लोज Group by से पहेले आता है जब की Having क्लोज Group
by के बाद में आता है
Where
क्लोज का उपयोग करने पर सारी row पहेले filter हो जाती है बादमे aggregation होता
है.
Having
क्लोज का उपयोग करने पर पहेले aggregation होता है बादमे row filter होती है
इस
लिये आप Where Clouse के साथ aggreate function नहीं use कर सकते. जब की Having
क्लोज के साथ आप aggregate function use कर सकते हो.
If you have any questions or suggestions please write in comments...
ReplyDelete