OraFAQ Forum: SQL & PL/SQL » can we add Alias column name to Group by?

Home » SQL & PL/SQL » SQL & PL/SQL » can we add Alias column name to Group by? (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

can we add Alias column name to Group by? [message #519239]

Wed, 10 August 2011 08:43

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Hi,

I have the following query:

SELECT AGENCY, COUNT(*)
FROM (SELECT A.AGENCY,SUM(A.NUM_LOGIN)
FROM GOVAGENCY A
WHERE AGENCY = 'DOD1'
GROUP BY A.AGENCY
UNION
SELECT B.AGENCY, SUM(B.NUM_LOGIN)
FROM GOVAGENCY B
WHERE AGENCY = 'DOD2'
GROUP BY B.AGENCY)
group by ROLLUP( AGENCY) ;

The results are:

AGECNY COUNT(*)
-------- -----------
DOD1 1
DOD2 1

2

The rollup value has a blank label instead I want to have a label to the rollup result also ex:

AGENCY COUNT(*)
--------- -----------
DOD1 1
DOD2 1

DEPT. OF DEFENSE 2

Any help is appreciated.

Thanks

Report message to a moderator

Re: can we add Alias column name to Group by? [message #519240 is a reply to message #519239]

Wed, 10 August 2011 08:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: can we add Alias column name to Group by? [message #519244 is a reply to message #519239]

Wed, 10 August 2011 09:09

flyboy
Messages: 1903
Registered: November 2006

Senior Member

gentleman777us wrote on Wed, 10 August 2011 15:43

The rollup value has a blank label instead I want to have a label to the rollup result also ex:

Something like this example: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066443 ? (although it groups by cube, but with rollup it would work the same way - DECODE is present only in SELECT clause, GROUP BY clause is unchanged).

Report message to a moderator

Re: can we add Alias column name to Group by? [message #519257 is a reply to message #519239]

Wed, 10 August 2011 09:50

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Report message to a moderator

Re: can we add Alias column name to Group by? [message #519265 is a reply to message #519257]

Wed, 10 August 2011 10:16

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Use NVL + GROUPING. For example:

SQL> select  job,
  2          sum(sal)
  3    from  emp
  4    group by rollup(job)
  5  /

JOB         SUM(SAL)
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
               29025

6 rows selected.

SQL> select  nvl(job,'Total') job,
  2          sum(sal)
  3    from  emp
  4    group by rollup(job)
  5    order by grouping(emp.job)
  6  /

JOB         SUM(SAL)
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
Total          29025

6 rows selected.

SQL>

SY.

Report message to a moderator

Re: can we add Alias column name to Group by? [message #519276 is a reply to message #519265]

Wed, 10 August 2011 12:17

gentleman777us
Messages: 122
Registered: April 2005

Senior Member

Thanks! for your response, it works.

Report message to a moderator

Previous Topic:	ORA-01489: result of string concatenation is too long
Next Topic:	Need help on converting columns of table as rows(2 Merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 23 02:55:29 CDT 2025