Thread: Grouping My query

Grouping My query

From
"Martin Kuria"
Date:
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

Re: Grouping My query

From
Richard Huxton
Date:
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

Re: Grouping My query

From
"Martin Kuria"
Date:
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/


Re: Grouping My query

From
Ron Johnson
Date:
-----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-----