Group by function

[Sherylj]

Group by function

Can you use a concentenated column in a group by? I keep getting an error when I try.

ord_store||' - '||mrc_desc "Yard"

I've tried using just ord_store and the whole string but it doesn't like either.

Thanks,
Sheryl

 

[ThomVF]

SQL> connect scott/tiger
SQL> select job||'-'||sal, count(*)
2 from emp
3 group by job||'-'||sal
4 /

JOB||'-'||SAL COUNT(*)
-------------------------------------------------- ----------
ANALYST-3000 2
CLERK-1100 1
CLERK-1300 1
CLERK-800 1
CLERK-950 1
MANAGER-2450 1
MANAGER-2850 1
MANAGER-2975 1
PRESIDENT-5000 1
SALESMAN-1250 2
SALESMAN-1500 1

JOB||'-'||SAL COUNT(*)
-------------------------------------------------- ----------
SALESMAN-1600 1

12 rows selected.

 

[Sherylj]

How does the Group By function work? I'm a newbie with Oracle PL/SQL and it seems to behave strangely.

I'm getting this error when I try your idea.

ORA-00979: not a GROUP BY expression

Do I have to include every selected item in my group by? I have several selected columns.

Thanks,
Sheryl

 

[ThomVF]

Let's see the actual query you are using.

ORACLE's definition of GROUP BY:
=========================================================
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
=========================================================

If you have a column in the SELECT list along with aggregate functions, that is a GROUP BY expression and it must be included in the GROUP BY at the bottom.
select job, avg(sal)
from emp
group by job

select job, mgr, avg(sal)
from emp
group by job,mgr

You can also group by values from the table being selected,even if they are not in the SELECT statement:
# Show average salary by dept without showing dept
select avg(sal)
from emp
group by job;

 

[Sherylj]

OK That helps. Thank you. I'll try it.

Sheryl

 

[Sherylj]

It works! Thanks a million...

Sheryl