Dzidza nezveKushandisa Microsoft Access GROUP BY Query

Iwe unogona kushandisa zvinyorwa zveSQL zvinyorwa kuti uwane deta kubva kune d database asi izvi kazhinji hazvipi kupa huchenjeri hwakakwana kuti uwane zvido zvebhizimisi. SQL inopawo iwe nekwanisi yekugadzirisa mhinduro dzemhinduro zvichienderana nemafungiro emasero-mutsara kuitira kuti ushandise maitiro akawandisa uchishandisa GROUP BY clause. Chimbofunga, somuenzaniso, urongwa hwetafura yedhafura rine maitiro ari pasi apa:

• OrderID - kukosha kwenhamba kunonyanya kuratidza hurongwa hwega. Iyi munda ndiyo inonyanya kukosha ye database.
• Mutengesi - mutauro unokosha kupa zita rekutengesa uyo akatengesa mishonga. Iyi munda iri kiyi yekunze kune imwe tafura ine mashoko evashandi.
• CustomerID - nhamba yepamusoro inowirirana nenhamba yekambani yemuteereri. Iyi munda ichine kiyi yekunze, kuratidzira tafura ine chinyorwa chekambani yemashoko.
• Revenue - a numeric value inowirirana nedhora rekutsvaga.

Apo panouya nguva yekutungamirira maitiro ekutengeswa kwevatengesi, Mutemo weAraders une mashoko anokosha anogona kushandiswa pakuongorora ikoko. Kana ukaongorora Jim, iwe unogona, somuenzaniso, kunyora mubvunzo wakajeka unotora zvose zveJim zvinyorwa zvese:

Sarudza * Kubva KUTAURA Mazano AII PAPA WAKATAURI AKAITA 'Jim'

Izvi zvingatora zvose zvinyorwa kubva kune database yakabatana nekutengesa kwakaitwa naJim:

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

Iwe unogona kudzokorora ruzivo urwu uye kuita mamwe maverengi ezvinyorwa kuti uuye nehuwandu hwemashoko, asi izvi zvingava basa rakaoma iwe waunofanira kudzokorora kune mumwe mutengesi mune kambani. Panzvimbo pezvo, unogona kutora basa iri neGIROUP imwe nemubvunzo iyo inotora nhamba dzevatengesi vese mukambani. Iwe unongonyora mubvunzo uye unotsanangura kuti dhesi yacho inofanira kuunganidza mhinduro inobva kune Sales Sales munda. Iwe unogona kushandisa imwe yeSQL aggregate mabasa kuti uite maverengera pamigumisiro.

Heino muenzaniso. Kana iwe ukaita sarudzo inotevera yeSQL:

SHE (Revenue) AS 'Total', MIN (Revenue) AS 'Smallest', MAX (Revenue) AS 'Yakanyanya Kupfuura', AVG (Revenue) AS 'Average', COUNT (Revenue) AS 'Number' Kubva Kutevedzera GROUP BY Salesperson

Iwe unogona kuwana mitezo inotevera:

Mutengesi Chidiki Chese Chikuru Kupfuura Chidiki Pakati Peji Jim 250000 10000 90000 50000 5 Mariya 342000 24000 102000 57000 6 Bob 118000 4000 36000 39333 3

Sezvaunogona kuona, basa iri rine simba rinokubvumira kuti uite zvinyorwa zviduku kubva mukati memubvunzo weSQL, uchipa bhizimusi rinokosha bhizimisi kune maneja ari kuita maitiro ekushanda. Gwaro reGroup par clause rinowanzoshandiswa mumashoko ezvinyorwa nekuda kwechinangwa ichi uye chinhu chinokosha muhomwe yeDBA yezvinyorwa.