Problem with grouping. I get a new row for each entry. SQL inside.

I need to pull data as in the query below.  The intent is to pull a row for each o.PRJ_ETNUMBER with the b.slice for each day as in the select statement.

I get the data okay, but for each day, I get a new row.  I've tried gouping by o.prj_etnumber and still get a new row for each day (SAT, SUN...)

Any ideas on how I can get these all on one row?  Here's the SQL:

Select
o.PRJ_ETNUMBER ET_NUMBER,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sat', b.slice) SAT,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sun', b.slice) SUN,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Mon', b.slice) MON,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Tue', b.slice) TUE,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Wed', b.slice) WED,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Thu', b.slice) THU,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Fri', b.slice) FRI
From
..........tables.........
Where
....joins here....  
( (    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') AND
SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
) OR
(    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') - 1 AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Sat', 'Sun')
))

Ideas?    Thanks in advance.
Weller0123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbeneitCommented:
you can post something example please
0
Weller0123Author Commented:
Here's the SQL.  I can't understand why each date is on it's own row.

In my test data there is only one value for o.prj_etnumber and there will always only be one entry for each day of the week.

------

Select
o.PRJ_ETNUMBER ET_NUMBER,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sat', b.slice) SAT,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sun', b.slice) SUN,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Mon', b.slice) MON,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Tue', b.slice) TUE,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Wed', b.slice) WED,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Thu', b.slice) THU,
DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Fri', b.slice) FRI
From
prj_blb_slices b,
prj_blb_slicerequests s,
prassignment a,
prtimeentry te,
prtask t,
srm_projects p,
odf_ca_project o,
srm_resources r

Where
r.full_name LIKE 'W%' AND
p.id = o.id and
b.slice_request_id = s.id and
s.id = '5000025' and
b.prj_object_id = te.PRID and
te.PRASSIGNMENTID = a.prid and
a.prtaskid = t.prid and
t.prprojectid = p.id and
a.prresourceid = r.id and
( (    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') AND
SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
) OR
(    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') - 1 AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Sat', 'Sun')
))
0
Weller0123Author Commented:
...and my output looks like this:

ET_NUMBER      SAT      SUN      MON      TUE      WED      THU      FRI
622201                  5                        
622201                        8                  
622201                              11            
622201                                    8      
622201                                          8

Instead of like this.  

ET_NUMBER      SAT      SUN      MON      TUE      WED      THU      FRI
622201                  5      8      11      8      8

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

dbeneitCommented:
ok
you have 1 record for each date in prj_blb_slices table then you obtain one record for each record

you can use a GROUP BY o.PRJ_ETNUMBER
select
o.PRJ_ETNUMBER ET_NUMBER,
sum(case when SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3)='Sat' then  b.slice else 0 end )SAT,
sum(case when SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3)='Sun'then  b.slice else 0 end )SUN,
sum(case when SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3)='Mon'then  b.slice else 0 end )MON,
sum(case when SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3)='Tue'then  b.slice else 0 end )TUE,
.....................
from
...............
where
.............
(    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') - 1 AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Sat', 'Sun')
))
GROUP BY o.PRJ_ETNUMBER
0
Weller0123Author Commented:
GROUP BY o.PRJ_ETNUMBER ...pulls the same results as shown above.

I don't understand why, but with the group by, I still get 7 separate rows as shown above.
0
dbeneitCommented:
Excuse me,  are you sure?
if you only have 8 columns  and you group by the 1est and the other 7 columns are obtained with sum , you have to obtain 1 row no 7
0
Weller0123Author Commented:
I'm sure.  ...and confused.

I think it has something to do with pulling b.slice over and over.

I don't understand a way around it though.
0
dbeneitCommented:
ok  

select  ET_NUMBER, sum (SAT) AS SAT ,sum(SUN)AS SUN,sum(MON) AS MON,sum(TUE) AS TUE,sum(WED) AS WED,sum(THU) AS THU,sum(FRI) as FRI
FROM
(select....
group by  o.PRJ_ETNUMBER ) as newtable
GROUP BY ET_NUMBER

AND THIS?
0
dbeneitCommented:
but i don't understand
0
Weller0123Author Commented:
Even as an inline, I get multiple rows.

Maybe I need to get the b.slice different.  I don't know here.
0
NicksonKohCommented:
Hi,

You need to sum the slice and group the et number. If slice can have null values, you need to add NVL() to b.slice in the summing.

SELECT   o.prj_etnumber et_number,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Sat', b.slice,
                      0
                     )
             ) sat,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Sun', b.slice,
                      0
                     )
             ) sun,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Mon', b.slice,
                      0
                     )
             ) mon,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Tue', b.slice,
                      0
                     )
             ) tue,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Wed', b.slice,
                      0
                     )
             ) wed,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Thu', b.slice,
                      0
                     )
             ) thu,
         SUM (DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),
                      'Fri', b.slice,
                      0
                     )
             ) fri
    FROM prj_blb_slices b,
         prj_blb_slicerequests s,
         prassignment a,
         prtimeentry te,
         prtask t,
         srm_projects p,
         odf_ca_project o,
         srm_resources r
   WHERE r.full_name LIKE 'W%'
     AND p.ID = o.ID
     AND b.slice_request_id = s.ID
     AND s.ID = '5000025'
     AND (    b.prj_object_id = te.prid
          AND te.prassignmentid = a.prid
          AND a.prtaskid = t.prid
          AND t.prprojectid = p.ID
          AND a.prresourceid = r.ID
         )
     AND (   (    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww')
              AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN
                                          ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
             )
          OR (    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') - 1
              AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN
                                                               ('Sat', 'Sun')
             )
         )
GROUP BY o.prj_etnumber
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
Weller0123,

In future, please don't cross post this type of question in the MS SQL Server Zone.

Thanks,
Anthony
0
NicksonKohCommented:
Hi administrator, this is actually more appropriately an Oracle question since the SQL syntax is Oracle.
0
Anthony PerkinsCommented:
I agree.
0
imarediaCommented:
Try this:

Select
o.PRJ_ETNUMBER ET_NUMBER,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sat', b.slice)) SAT,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Sun', b.slice)) SUN,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Mon', b.slice)) MON,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Tue', b.slice)) TUE,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Wed', b.slice)) WED,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Thu', b.slice)) THU,
MAX(DECODE (SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3),'Fri', b.slice)) FRI
From
prj_blb_slices b,
prj_blb_slicerequests s,
prassignment a,
prtimeentry te,
prtask t,
srm_projects p,
odf_ca_project o,
srm_resources r

Where
r.full_name LIKE 'W%' AND
p.id = o.id and
b.slice_request_id = s.id and
s.id = '5000025' and
b.prj_object_id = te.PRID and
te.PRASSIGNMENTID = a.prid and
a.prtaskid = t.prid and
t.prprojectid = p.id and
a.prresourceid = r.id and
( (    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') AND
SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Mon', 'Tue', 'Wed', 'Thu', 'Fri')
) OR
(    TO_CHAR (b.slice_date, 'ww') = TO_CHAR (SYSDATE, 'ww') - 1 AND SUBSTR (TO_CHAR (b.slice_date, 'Day'), 1, 3) IN ('Sat', 'Sun')
))
GROUP BY o.PRJ_ETNUMBER ET_NUMBER


Here is the simple example:
---------------------------------
CREATE TABLE t1
( et_num     number,
  dy_sun     number,
  dy_mon     number,
  dy_tue     number,
  dy_wed     number,
  dy_thu     number,
  dy_fri     number,
  dy_sat     NUMBER
);


INSERT INTO t1(et_num, dy_sun) VALUES (622201, 5);
INSERT INTO t1(et_num, dy_mon) VALUES (622201, 1);
INSERT INTO t1(et_num, dy_tue) VALUES (622201, 8);
INSERT INTO t1(et_num, dy_wed) VALUES (622201, 11);
INSERT INTO t1(et_num, dy_thu) VALUES (622201, 52);
INSERT INTO t1(et_num, dy_fri) VALUES (622201, 15);
INSERT INTO t1(et_num, dy_sat) VALUES (622201, 6);

commit;

SELECT   et_num, max(dy_sun), max(dy_mon), max(dy_tue),
         max(dy_wed), max(dy_thu), max(dy_fri), max(dy_sat)
  FROM   t1
GROUP BY et_num;

Hope this helps.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.