Participants
Martin Kuria
Martin Kuria
Richard Huxton
Richard Huxton
Ron Johnson
Ron Johnson
Attachments
Show all attachments
Thread Outline
#1Martin KuriaMartin Kuria
Nov 01, 2006
#2Richard Huxton↳Richard Huxtonto Martin Kuria (#1)
Nov 01, 2006
#3Martin Kuria↳Martin Kuriato Richard Huxton (#2)
Nov 02, 2006
#4Ron Johnson↳Ron Johnsonto Martin Kuria (#3)
Nov 02, 2006

Grouping My query

Started by Martin Kuriaover 19 years ago4 messagesgeneral
Jump to latest
#1Martin Kuria
Martin Kuria
martinkuria@hotmail.com

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/

Attachments:

GROUPBY.txttext/plain; format=flowed; name=GROUPBY.txtDownload
#2Richard Huxton
Richard Huxton
dev@archonet.com
In reply to: Martin Kuria (#1)
Re: Grouping My query

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

#3Martin Kuria
Martin Kuria
martinkuria@hotmail.com
In reply to: Richard Huxton (#2)
Re: Grouping My query

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/

#4Ron Johnson
Ron Johnson
ron.l.johnson@cox.net
In reply to: Martin Kuria (#3)
Re: Grouping My query

-----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-----

← Back to Topics