Koyi game da Amfani da Microsoft Access GROUP BY Query

Kuna iya amfani da ƙididdiga na asali na asali don dawo da bayanan daga ɗakin yanar gizo amma wannan sau da yawa ba ya samar da cikakkun bayanai don saduwa da bukatun kasuwanci. SQL kuma yana baka damar da za a samo sakamakon tambayoyin da aka danganta a kan halayen jere-jita don yin amfani da ayyukan tara tare ta amfani da GROUP BY clause. Ka yi la'akari da, misali, daftarin bayanai da ke kunshe da halayen da ke ƙasa:

• OrderID - Ƙididdiga mai mahimmanci da aka gano kowane tsari. Wannan filin shine maɓallin farko don database.
• Mai sayarwa - wani nau'in rubutu mai suna sunan mai sayar da tallan wanda ya sayar da samfurori. Wannan filin shine maɓallin waje na waje zuwa wani tebur wanda ya ƙunshi bayanin ma'aikata.
• CustomerID - darajar lambobi daidai da lambar lissafin abokin ciniki. Wannan filin kuma maɓallin ƙetare ne, yana magana akan tebur wanda ke dauke da bayanin asusun abokin ciniki.
• Revenue - Daidaita farashin daidai da adadin dollar na sayarwa.

Lokacin da ya zo lokaci don gudanar da sake dubawa ga masu sayar da tallace-tallace, ɗakin Umurnai yana dauke da bayanai mai mahimmanci waɗanda za a iya amfani dasu don wannan bita. Lokacin da ake gwada Jim, za ka iya, alal misali, rubuta wani abu mai sauƙi wanda ya dawo da duk wani asusun ajiyar Jim:

SANTA * DAGA KARANTA KADA SAKAYA YAKE 'Jim'

Wannan zai dawo da dukkan bayanan da suka dace daga kamfanoni masu dacewa da tallace-tallace da Jim ya sanya:

OrderID Salesperson CustomerID Revenue 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000

Kuna iya nazarin wannan bayanin kuma kuyi wasu lissafin labarun don ku zo da kididdigar ayyuka, amma wannan zai zama babban aiki da za ku yi maimaitawa ga kowane mai sayarwa a kamfanin. Maimakon haka, zaka iya maye gurbin wannan aiki tare da ƙungiyar GROUP BY QUery wanda ke lissafta kididdigar kowane mai sayarwa a kamfanin. Kuna rubuta rubutun kawai sannan ku bayyana cewa database ya kamata ya ƙunshi sakamakon da ya danganci filin tallace-tallace. Hakanan zaka iya amfani da kowane nau'in ayyuka na SQL don yin lissafin akan sakamakon.

Ga misali. Idan ka kashe wadannan sanarwa na SQL:

SAN MAISU, SUM (Asusun) AS 'Ƙari', MIN (Asusun) AS 'Mafi ƙanƙanci', MAX (Asusun) AS 'Mafi Girma', AVG (Asusun) AS 'Matsayin', COUNT (Asusun) AS 'Lambar' Daga Kayan Gida GROUP BY Salesperson

Za ku sami sakamako masu zuwa:

Salesperson Total Mafi ƙanƙanci mafi girma Average Number Jim 250000 10000 90000 50000 5 Mary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Kamar yadda kake gani, wannan aiki mai iko ya ba ka damar samar da kananan rahotanni daga cikin tambaya na SQL, samar da kyakkyawan ƙwarewar kasuwanci ga mai sarrafa manajan nazari. Ƙungiyar ta GRUP ta hanyar amfani da ita an yi amfani dashi a cikin bayanai don wannan dalili kuma yana da kayan aiki mai mahimmanci a cikin jaka na DBA.