Thread: Grouping My query
Dear Sir/Madam,
I have a staff directory, which group staff in various categories I would
like to group the staff members in there specific categories,
Below is query is query I am running, it works fine and returns the output
below. My question is, how do I Group the staff members in there respective
categories using the staff_catid(Category Table id) ,
staff_subcatid(Sub_Category Table id) fields.
SELECT
staff_lname,staff_fname,staff_id,staff_catid,staff_subcatid,cat_acron,subcat_acron,staff_sortorder
FROM staffmembers
LEFT OUTER JOIN category ON cat_id = staff_catid
LEFT OUTER JOIN sub_category ON subcat_id = staff_subcatid
INNER JOIN usercat_mode ON mod_mode_id = staff_s_subcatid AND mod_user_id =
'7146'
ORDER BY staff_sortorder;
staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid |
cat_acron | subcat_acron | staff_sortorder
-------------+-------------+----------+-------------+----------------+-----------+--------------+-----------------
Vacant | Vacant | 8836 | 500 | 534 | ADSDS
| ODA | 0
nnnnn | aaaaa | 7148 | 500 | 534 | ADSDS
| ODA | 0
bbbbb | Sbbbbb | 7150 | 500 | 534 | ADSDS
| WEB | 1
xxxxx | cccccc | 7174 | 500 | 534 | ADSDS
| WEB | 1
eeeee | dddddd | 7173 | 500 | 534 | ADSDS
| WEB | 2
nnnnn | eeeee | 7149 | 500 | 534 | NIS
| EDCU | 2
ppppp | Axxxx | 7156 | 500 | 534 | NIS
| EDCU | 3
iiiiii | Rmmmm | 7175 | 500 | 534 | NIS
| EDCU | 3
Kung | Wfffff | 7147 | 500 | 534 | NIS
| INSU | 4
uuuuu | Martin | 7178 | 500 | 534 | NIS
| INSU | 4
oooooo | eeeee | 7179 | 500 | 534 | NIS
| INSU | 5
wwwww | Mary | 7146 | 500 | 534 | NIS
| INSU | 5
lllll | wwwwwww | 7151 | 500 | 534 | NIS
| INSU | 6
wwwwww | Cttttt | 7145 | 500 | 534 | QAUSS
| CS | 7
none | none | 7152 | 500 | 534 | QAUSS
| CS | 8
eeee | Hmmmmm | 7155 | 500 | 534 | QAUSS
| CS | 9
eeeee | Bdddd | 7153 | 500 | 534 | QAUSS
| CS | 10
yyyy | Wjjjj | 7157 | 500 | 534 | QAUSS
| IT | 11
None | None | 7158 | 500 | 534 | QAUSS
| IT | 12
ttttt | Ryyyyrd | 8825 | 500 | 534 | QAUSS
| IT | 13
none | none | 7163 | 500 | 534 | QAUSS
| IT | 14
uuuu | rrrrr | 7160 | 500 | 534 | QAUSS
| IT | 15
mmmmm | John | 8838 | 500 | 534 | QAUSS
| IT | 16
66 | 666 | 9341 | 500 | 534 | QAUSS
| SATU | 17
vvvvv | Pradeep | 7161 | 500 | 534 | QAUSS
| SATU | 18
aaaaa | Pamela | 7164 | 500 | 534 | QAUSS
| SATU | 19
Below is an output I would like to achieve can this be achived my using the
Group by or I can write a script to achieve the output below please do
assist. As you can see the staff members are group in their respective
Categories and Subcategory unlike the output above.
staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid |
cat_acron | subcat_acron | staff_sortorder
-------------+-------------+----------+-------------+----------------+-----------+--------------+-----------------
ADSDS
ODA
Vacant | Vacant | 8836 | 500 | 534 |
ADSDS | ODA | 0
nnnnn | aaaaa | 7148 | 500 | 534 |
ADSDS | ODA | 0
WEB
bbbbb | Sbbbbb | 7150 | 500 | 534 |
ADSDS | WEB | 1
xxxxx | cccccc | 7174 | 500 | 534 |
ADSDS | WEB | 1
eeeee | dddddd | 7173 | 500 | 534 |
ADSDS | WEB | 2
NIS
EDCU
nnnnn | eeeee | 7149 | 500 | 534 | NIS
| EDCU | 2
ppppp | Axxxx | 7156 | 500 | 534 | NIS
| EDCU | 3
iiiiii | Rmmmm | 7175 | 500 | 534 | NIS
| EDCU | 3
INSU
Kung | Wfffff | 7147 | 500 | 534 | NIS
| INSU | 4
uuuuu | Martin | 7178 | 500 | 534 | NIS
| INSU | 4
oooooo | eeeee | 7179 | 500 | 534 | NIS
| INSU | 5
wwwww | Mary | 7146 | 500 | 534 | NIS
| INSU | 5
lllll | wwwwwww | 7151 | 500 | 534 | NIS
| INSU | 6
QAUSS
CS
wwwwww | Cttttt | 7145 | 500 | 534 |
QAUSS | CS | 7
none | none | 7152 | 500 | 534 |
QAUSS | CS | 8
eeee | Hmmmmm | 7155 | 500 | 534 |
QAUSS | CS | 9
eeeee | Bdddd | 7153 | 500 | 534 |
QAUSS | CS | 10
IT
yyyy | Wjjjj | 7157 | 500 | 534 |
QAUSS | IT | 11
None | None | 7158 | 500 | 534 |
QAUSS | IT | 12
ttttt | Ryyyyrd | 8825 | 500 | 534 |
QAUSS | IT | 13
none | none | 7163 | 500 | 534 |
QAUSS | IT | 14
uuuu | rrrrr | 7160 | 500 | 534 |
QAUSS | IT | 15
mmmmm | John | 8838 | 500 | 534 |
QAUSS | IT | 16
SATU
66 | 666 | 9341 | 500 | 534 |
QAUSS | SATU | 17
vvvvv | Pxxxxxx | 7161 | 500 | 534 |
QAUSS | SATU | 18
aaaaa | Pamela | 7164 | 500 | 534 |
QAUSS | SATU | 19
Please use the attached document GROUPBY.txt to view the output in a neat
format.
Kind Regards
Martin W. Kuria
_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
Attachment
Martin Kuria wrote: > Dear Sir/Madam, > > I have a staff directory, which group staff in various categories I > would like to group the staff members in there specific categories, > > Below is an output I would like to achieve can this be achived my using > the Group by or I can write a script to achieve the output below please > do assist. As you can see the staff members are group in their > respective Categories and Subcategory unlike the output above. > > staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | > cat_acron | subcat_acron | staff_sortorder > -------------+-------------+----------+-------------+----------------+-----------+--------------+----------------- > > ADSDS > ODA > Vacant | Vacant | 8836 | 500 | 534 | > ADSDS | ODA | 0 > nnnnn | aaaaa | 7148 | 500 | 534 | > ADSDS | ODA | 0 > WEB > bbbbb | Sbbbbb | 7150 | 500 | 534 | > ADSDS | WEB | 1 > xxxxx | cccccc | 7174 | 500 | 534 | > ADSDS | WEB | 1 > eeeee | dddddd | 7173 | 500 | 534 | > ADSDS | WEB | 2 Am I right in thinking you're trying to do headings here? heading1: ADSDS heading2: ODA ...rows... heading2: WEB ...rows... This is a formatting issue - do it in your client code. There are report -generating systems available for many languages. -- Richard Huxton Archonet Ltd
Dear Richard, Thanks for your response, you are right I am trying to do headings, I thought I could do it from writing a SQL statement, but as you have suggested it can only be achieved on the client code, I wish one day it can be achieved with a SQL Statament thanks again . Kind Regards. +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ >From: Richard Huxton <dev@archonet.com> >To: Martin Kuria <martinkuria@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Grouping My query >Date: Wed, 01 Nov 2006 18:50:46 +0000 > >Martin Kuria wrote: >>Dear Sir/Madam, >> >>I have a staff directory, which group staff in various categories I would >>like to group the staff members in there specific categories, > >> >>Below is an output I would like to achieve can this be achived my using >>the Group by or I can write a script to achieve the output below please do >>assist. As you can see the staff members are group in their respective >>Categories and Subcategory unlike the output above. >> >>staff_lname | staff_fname | staff_id | staff_catid | staff_subcatid | >>cat_acron | subcat_acron | staff_sortorder >>-------------+-------------+----------+-------------+----------------+-----------+--------------+----------------- >> >>ADSDS >>ODA >> Vacant | Vacant | 8836 | 500 | 534 | >>ADSDS | ODA | 0 >> nnnnn | aaaaa | 7148 | 500 | 534 | >>ADSDS | ODA | 0 >>WEB >> bbbbb | Sbbbbb | 7150 | 500 | 534 | >>ADSDS | WEB | 1 >> xxxxx | cccccc | 7174 | 500 | 534 | >>ADSDS | WEB | 1 >> eeeee | dddddd | 7173 | 500 | 534 | >>ADSDS | WEB | 2 > >Am I right in thinking you're trying to do headings here? > heading1: ADSDS > heading2: ODA > ...rows... > heading2: WEB > ...rows... > >This is a formatting issue - do it in your client code. There are report >-generating systems available for many languages. > >-- > Richard Huxton > Archonet Ltd _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/01/06 23:43, Martin Kuria wrote: > Dear Richard, > > Thanks for your response, you are right I am trying to do headings, I > thought I could do it from writing a SQL statement, but as you have > suggested it can only be achieved on the client code, I wish one day it > can be achieved with a SQL Statament thanks again . You can do it in PL/pgSQL. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFSfvMS9HxQb37XmcRAp/PAKCZN+KqofcbbitG4RdAQggspANOWQCg68vT d0/UAVvZFLZlfLWjSRtmga4= =fftQ -----END PGP SIGNATURE-----