Grouping My query - Mailing list pgsql-general
| From | Martin Kuria |
|---|---|
| Subject | Grouping My query |
| Date | |
| Msg-id | BAY117-F232C6C7C5F8E35A4A7F190A6F80@phx.gbl Whole thread Raw |
| Responses |
Re: Grouping My query
|
| List | pgsql-general |
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
pgsql-general by date: