Solved

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

Posted on 2007-03-22
15
194 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:Weller0123
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
you can post something example please
0
 

Author Comment

by:Weller0123
Comment Utility
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
 

Author Comment

by:Weller0123
Comment Utility
...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
 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
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
 

Author Comment

by:Weller0123
Comment Utility
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
 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
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
 

Author Comment

by:Weller0123
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
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
 
LVL 9

Expert Comment

by:dbeneit
Comment Utility
but i don't understand
0
 

Author Comment

by:Weller0123
Comment Utility
Even as an inline, I get multiple rows.

Maybe I need to get the b.slice different.  I don't know here.
0
 
LVL 17

Accepted Solution

by:
NicksonKoh earned 500 total points
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Weller0123,

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

Thanks,
Anthony
0
 
LVL 17

Expert Comment

by:NicksonKoh
Comment Utility
Hi administrator, this is actually more appropriately an Oracle question since the SQL syntax is Oracle.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I agree.
0
 
LVL 3

Expert Comment

by:imaredia
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now