GROUP BY ISSUE
Author JP Vijaykumar Oracle DBA
Date Jan 4th 2017

--Recently I worked on an issue using group by clause.
--To simulate the issue, created a table and populated it with data.

drop table temp_jp;
create table temp_jp(run_date date, name varchar2(20),comm number);

truncate table temp_jp;
set serverout on size 1000000 timing on
declare
v_dt date:=sysdate +28;
begin
for i in 1..4 loop
for j in 1..3 loop
insert into temp_jp values(v_dt,'VINEELA',1000*i +j*50);
insert into temp_jp values(v_dt,'VEEKSHA',1000*i +j*2*100);
insert into temp_jp values(v_dt,'SAKETH',1000*i+j*150);
end loop;
v_dt:=v_dt -1;
end loop;
end;
/

set linesize 60 pagesize 60
select * from temp_jp order by 1,2;

RUN_DATE NAME COMM
--------- -------------------- ----------
01-JAN-17 SAKETH 4300
01-JAN-17 SAKETH 4450
01-JAN-17 SAKETH 4150
01-JAN-17 VEEKSHA 4600
01-JAN-17 VEEKSHA 4200
01-JAN-17 VEEKSHA 4400
01-JAN-17 VINEELA 4050
01-JAN-17 VINEELA 4150
01-JAN-17 VINEELA 4100
02-JAN-17 SAKETH 3450
02-JAN-17 SAKETH 3150
02-JAN-17 SAKETH 3300
02-JAN-17 VEEKSHA 3400
02-JAN-17 VEEKSHA 3200
02-JAN-17 VEEKSHA 3600
02-JAN-17 VINEELA 3100
02-JAN-17 VINEELA 3050
02-JAN-17 VINEELA 3150
03-JAN-17 SAKETH 2300
03-JAN-17 SAKETH 2150
03-JAN-17 SAKETH 2450
03-JAN-17 VEEKSHA 2600
03-JAN-17 VEEKSHA 2200
03-JAN-17 VEEKSHA 2400
03-JAN-17 VINEELA 2150
03-JAN-17 VINEELA 2100
03-JAN-17 VINEELA 2050
04-JAN-17 SAKETH 1150
04-JAN-17 SAKETH 1450
04-JAN-17 SAKETH 1300
04-JAN-17 VEEKSHA 1400
04-JAN-17 VEEKSHA 1200
04-JAN-17 VEEKSHA 1600
04-JAN-17 VINEELA 1050
04-JAN-17 VINEELA 1100
04-JAN-17 VINEELA 1150

36 rows selected.

--The user wants sum of commission of each member on the available dates.

select run_date,
sum(decode(substr(name,1,6),'VINEEL',comm,0)) vine_comm,
sum(decode(substr(name,1,5),'VEEKS',comm,0)) veek_comm,
sum(decode(substr(name,1,4),'SAKE',comm,0)) sake_comm
from temp_jp group by run_date,name order by run_date;

RUN_DATE VINE_COMM VEEK_COMM SAKE_COMM
--------- ---------- ---------- ----------
01-JAN-17 0 0 12900
01-JAN-17 0 13200 0
01-JAN-17 12300 0 0
02-JAN-17 0 0 9900
02-JAN-17 0 10200 0
02-JAN-17 9300 0 0
03-JAN-17 0 0 6900
03-JAN-17 0 7200 0
03-JAN-17 6300 0 0
04-JAN-17 0 0 3900
04-JAN-17 0 4200 0
04-JAN-17 3300 0 0

12 rows selected.

--Expected the above query returns sum(comm) values for each member for the 4 days.
--Instead, it displayed 12 rows.

select trunc(run_date) run_date,
decode(substr(name,1,6),'VINEEL',sum(comm),0) vine_comm,
decode(substr(name,1,5),'VEEKS',sum(comm),0) veek_comm,
decode(substr(name,1,4),'SAKE',sum(comm),0) sake_comm
from temp_jp group by trunc(run_date),name order by 1;

RUN_DATE VINE_COMM VEEK_COMM SAKE_COMM
--------- ---------- ---------- ----------
01-JAN-17 0 0 12900
01-JAN-17 0 13200 0
01-JAN-17 12300 0 0
02-JAN-17 0 0 9900
02-JAN-17 9300 0 0
02-JAN-17 0 10200 0
03-JAN-17 0 0 6900
03-JAN-17 0 7200 0
03-JAN-17 6300 0 0
04-JAN-17 3300 0 0
04-JAN-17 0 0 3900
04-JAN-17 0 4200 0

12 rows selected.

--No luck with, with the above modification.

select run_date run_date,
case when substr(name,1,5) = 'VINEE' then sum(comm) else 0 end vine_comm,
case when substr(name,1,5) = 'VEEKS' then sum(comm) else 0 end veek_comm,
case when substr(name,1,5) = 'SAKET' then sum(comm) else 0 end sake_comm
from temp_jp group by run_date;

--This above query failed with the following error:
ORA-00979: not a GROUP BY expression, on the name column.

select run_date run_date,
case when substr(name,1,5) = 'VINEE' then sum(comm) else 0 end vine_comm,
case when substr(name,1,5) = 'VEEKS' then sum(comm) else 0 end veek_comm,
case when substr(name,1,5) = 'SAKET' then sum(comm) else 0 end sake_comm
from temp_jp group by run_date,name order by 1;

RUN_DATE VINE_COMM VEEK_COMM SAKE_COMM
--------- ---------- ---------- ----------
01-JAN-17 0 0 12900
01-JAN-17 0 13200 0
01-JAN-17 12300 0 0
02-JAN-17 0 0 9900
02-JAN-17 0 10200 0
02-JAN-17 9300 0 0
03-JAN-17 0 0 6900
03-JAN-17 0 7200 0
03-JAN-17 6300 0 0
04-JAN-17 0 0 3900
04-JAN-17 0 4200 0
04-JAN-17 3300 0 0

12 rows selected.

--Query did not work.

--To generate the output in the required format, I used a work around.

with t as (
select run_date,
decode(substr(name,1,6),'VINEEL',comm,0) vine_comm,
decode(substr(name,1,5),'VEEKS',comm,0) veek_comm,
decode(substr(name,1,4),'SAKE',comm,0) sake_comm
from temp_jp )
select run_date,sum(vine_comm),sum(veek_comm),sum(sake_comm) from t group by run_date;

RUN_DATE SUM(VINE_COMM) SUM(VEEK_COMM) SUM(SAKE_COMM)
--------- -------------- -------------- --------------
02-JAN-17 9300 10200 9900
03-JAN-17 6300 7200 6900
04-JAN-17 3300 4200 3900
01-JAN-17 12300 13200 12900

--The above work around fetched the output in the required format.

with t as (select trunc(run_date) run_date,
case when substr(name,1,6) = 'VINEEL' then sum(comm) else 0 end vine_comm,
case when substr(name,1,5) = 'VEEKS' then sum(comm) else 0 end veek_comm,
case when substr(name,1,4) = 'SAKE' then sum(comm) else 0 end sake_comm
from temp_jp group by trunc(run_date),name)
select run_date,sum(vine_comm),sum(veek_comm),sum(sake_comm) from t group by run_date;

RUN_DATE SUM(VINE_COMM) SUM(VEEK_COMM) SUM(SAKE_COMM)
--------- -------------- -------------- --------------
03-JAN-17 6300 7200 6900
02-JAN-17 9300 10200 9900
04-JAN-17 3300 4200 3900
01-JAN-17 12300 13200 12900

--This variation also worked.

select run_date,sum(vine_comm),sum(veek_comm),sum(sake_comm) from (
select trunc(run_date) run_date,
decode(substr(name,1,6),'VINEEL',sum(comm),0) vine_comm,
decode(substr(name,1,5),'VEEKS',sum(comm),0) veek_comm,
decode(substr(name,1,4),'SAKE',sum(comm),0) sake_comm
from temp_jp group by trunc(run_date),name) group by run_date order by 1;

RUN_DATE SUM(VINE_COMM) SUM(VEEK_COMM) SUM(SAKE_COMM)
--------- -------------- -------------- --------------
01-JAN-17 12300 13200 12900
02-JAN-17 9300 10200 9900
03-JAN-17 6300 7200 6900
04-JAN-17 3300 4200 3900

--So also this variation.

select * from temp_jp
pivot (sum(comm) for (name) in (
'VINEELA' as vine_comm,
'VEEKSHA' as veek_comm,
'SAKETH' as sake_comm))
order by run_date;

RUN_DATE VINE_COMM VEEK_COMM SAKE_COMM
--------- ---------- ---------- ----------
01-JAN-17 12300 13200 12900
02-JAN-17 9300 10200 9900
03-JAN-17 6300 7200 6900
04-JAN-17 3300 4200 3900

--Pivot option worked for me too.

--I was curious as to why the query did not work as expected.
--Further debugging helped me.

select run_date,
sum(decode(substr(name,1,6),'VINEEL',comm,0)) vine_comm,
sum(decode(substr(name,1,6),'VEEKSH',comm,0)) veek_comm,
sum(decode(substr(name,1,6),'SAKETH',comm,0)) sake_comm
from temp_jp group by run_date order by 1;

RUN_DATE VINE_COMM VEEK_COMM SAKE_COMM
--------- ---------- ---------- ----------
01-JAN-17 12300 13200 12900
02-JAN-17 9300 10200 9900
03-JAN-17 6300 7200 6900
04-JAN-17 3300 4200 3900

--The above construct worked.

--I leave the detailed explanation to the readers' imagination.

--Happy scripting.