Home » SQL & PL/SQL » SQL & PL/SQL » group by for null column
group by for null column [message #198622] Wed, 18 October 2006 00:51 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
select sum(sal),deptno
from scott.emp
group by deptno

getting 3 rows in return for 10,20.30

can how can i get the data in this way
1000 10 10-1
2000 20 20-1
2500 30 30-1

this query does not help
select sum(sal),deptno,deptno||'-1'col
from scott.emp
group by deptno,col

can any one help

Re: group by for null column [message #198629 is a reply to message #198622] Wed, 18 October 2006 01:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
GROUP BY takes real columns not aliases or derived columns you use in your SELECT. ORDER BY can work with aliases and derived columns because the sort is applied AFTER the select has been executed. In your case, you only need to group by the department number:
SQL> select sum(salary) x
  2       , department_id
  3       , to_char(department_id)||'-1' col
  4  from   employees
  5  group  by department_id
  6  /

         X DEPARTMENT_ID COL
---------- ------------- ------------------------------------------
      4400            10 10-1
     19000            20 20-1
     24900            30 30-1
      6500            40 40-1
    156400            50 50-1
...<snip>...


MHE
Re: group by for null column [message #198646 is a reply to message #198622] Wed, 18 October 2006 02:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
select sum(sal),deptno,deptno||'-1'col
from scott.emp
group by deptno,col


In this case you need not add an extra group by clause

following will work

select sum(sal), deptno, deptno || '-1' from scott.emp
group by deptno;

Following query also works


SQL> select sum(salary), department_id, department_id || '-1'
2 from employees
3 group by department_id, department_id || '-1';

SUM(SALARY) DEPARTMENT_ID DEPARTMENT_ID||'-1'
----------- ------------- ------------------------------------------
6500 40 40-1
24900 30 30-1
304500 80 80-1
10000 70 70-1
20300 110 110-1
19000 20 20-1
4400 10 10-1
51600 100 100-1
58000 90 90-1
156400 50 50-1
7000 -1

SUM(SALARY) DEPARTMENT_ID DEPARTMENT_ID||'-1'
----------- ------------- ------------------------------------------
28800 60 60-1

12 rows selected.


You can mention derived columns output in group by but you cannot reference it with the alias instead you should mention the derived column as it is like in this case it will be department_id || '-1'.

cheers

Re: group by for null column [message #198654 is a reply to message #198646] Wed, 18 October 2006 02:30 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
S.Rajaram wrote on Wed, 18 October 2006 09:11
You can mention derived columns output in group by but you cannot reference it with the alias instead you should mention the derived column as it is like in this case it will be department_id || '-1'.
Correct, I was a bit off: aliases cannot be used in a GROUP BY, but the derived columns can. Thanks for the correction. But you don't need to use the derived column in the GROUP BY. A grouping on department id is enough in this case.

@Rajaram: A small tip. Use the tags [CODE] and [/CODE] around any code blocks to preserve formatting Wink

MHE
Previous Topic: execute a DTS using TSQL
Next Topic: best denomination notes
Goto Forum:
  


Current Time: Sun Dec 11 06:14:05 CST 2016

Total time taken to generate the page: 0.04551 seconds