Phunzirani Kugwiritsa Ntchito Microsoft Access GROUP BY Query

Mungagwiritse ntchito mafunso oyambirira a SQL kuti mupeze data kuchokera ku databata koma izi nthawi zambiri sichipatsa nzeru zokwanira kuti zikwaniritse zofunikira za bizinesi. SQL imakupatsaninso kuti mutha kuyambitsa zotsatira zazokambirana zogwirizana ndi zikhalidwe za mzere wa mzere kuti mugwiritse ntchito ntchito zambiri pogwiritsa ntchito clause GROUP BY. Taganizirani, mwachitsanzo, tebulo la deta ladongosolo lokhala ndi zizindikiro pansipa:

• OrderID - chiwerengero cha chiwerengero chodziwikiratu chokhazikika. Munda uwu ndichinsinsi chachikulu cha deta.
• Wogulitsa - mawu akupereka dzina la wogulitsa amene anagulitsa katunduyo. Munda umenewu ndi fungulo yachilendo ku gome lina lomwe lili ndi mauthenga ogwira ntchito.
• CustomerID - mtengo wamtengo wofanana ndi nambala ya akaunti ya kasitomala. Mundawu ndichinsinsi chachilendo, kutanthauzira tebulo lokhala ndi nkhani ya kasitomala.
• Ndalama - mtengo wamtengo wofanana ndi dola ya malonda.

Pakubwera nthawi yopenda ndondomeko za ogulitsa, gome la Malamulo liri ndi mfundo zofunika zomwe zingagwiritsidwe ntchito pazokambirana. Pofufuza Jim, mwachitsanzo, mukhoza kulemba funso losavuta limene limatenga zonse zomwe Jim akugulitsa:

CHISANKHO * CHOTCHOKERA PAMODZI WONSE WOTCHITSA KUTI ALI 'Jim'

Izi zikhoza kutengera zolemba zonse kuchokera ku databata zomwe zikugwirizana ndi malonda omwe Jim adalonjeza:

OrderID Wogulitsa Makhalidwe a Customerid 12482 Jim 182 40000 12488 Jim 219 25000 12519 Jim 137 85000 12602 Jim 182 10000 12741 Jim 155 90000

Mukhoza kupenda izi ndi kupanga zowerengera zina kuti mukhale ndi chiwerengero cha ntchito, koma ichi ndi ntchito yovuta yomwe muyenera kubwereza kwa wogulitsa aliyense pa kampaniyo. M'malo mwake, mungathe kubwezeretsa ntchitoyi ndi funso limodzi la GROUP NDI lomwe limawerengetsera chiwerengero kwa wogulitsa aliyense mu kampani. Mukungoyankha funsoli ndikuwonetseratu kuti mndandanda wazamasamba uyenera kugawana zotsatira zogwirizana ndi wogulitsa malonda. Mungagwiritse ntchito ntchito iliyonse ya SQL kupanga mawerengedwe pa zotsatira.

Nazi chitsanzo. Ngati munapanga chiganizo chotsatira cha SQL:

Sew (Revenue) AS 'Total', MIN (Revenue) AS 'Smallest', MAX (Revenue) AS 'Yaikulu', AVG (Revenue) AS 'Average', COUNT (Revenue) AS 'Number' FROM Malamulo GROUP NDI WOTCHITSA

Mungapeze zotsatira zotsatirazi:

Wogulitsa Zonse Zazikulu Kwambiri Average Number Jim 250000 10000 90000 50000 5 Mary 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Monga mukuonera, ntchito yayikuluyi ikukuthandizani kuti mupange mayankho aang'ono kuchokera mkati mwa funso la SQL, ndikupatsani nzeru zamalonda kwa meneti akuyendetsa ndemanga za ntchito. Gulu la GROUP BY limagwiritsidwa ntchito m'mabuku a zolinga izi ndipo ndi chida chamtengo wapatali mu thumba la DBA lachinyengo.