Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-22
15
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 9

Expert Comment

by:dbeneit
ID: 18776063
you can post something example please
0
 

Author Comment

by:Weller0123
ID: 18776154
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
ID: 18776177
...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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:dbeneit
ID: 18776223
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
ID: 18776337
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
ID: 18776377
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
ID: 18776515
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
 
LVL 9

Expert Comment

by:dbeneit
ID: 18776542
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
ID: 18776549
but i don't understand
0
 

Author Comment

by:Weller0123
ID: 18776686
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 2000 total points
ID: 18776746
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
ID: 18777273
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
ID: 18777299
Hi administrator, this is actually more appropriately an Oracle question since the SQL syntax is Oracle.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18777335
I agree.
0
 
LVL 3

Expert Comment

by:imaredia
ID: 18909244
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

715 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