OraFAQ Forum: SQL & PL/SQL » Group By / Order By

Home » SQL & PL/SQL » SQL & PL/SQL » Group By / Order By

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group By / Order By [message #7587]

Mon, 23 June 2003 16:47

Heidi
Messages: 37
Registered: February 2000

Member

Hello - I inherited a query and changed it around a bit, but now am getting the error "not a single-group group function". I know it has something to do with group by and order by, but i cannot figure out the correct combination.

i want my output to be like this:

facilitycode date responsibleparty basecur ownerTTL userTTL grandTTL
xyz 5-may-03 owner USD 50 50 100
xyz 10-may-03 user USD 40 76 116

Thanks to anyone htat can lend a hand!

select
fs.facilitycode as "Facility Code",
rep.localTimeRepairCompleted as "Repair Complete Date",
pa.responsibleParty as "Responsible Party",
es.baseCurrency as "Base Currency",
sum(
decode(pa.responsibleParty, 'Owner', pa.laborbase ) +
decode(pa.responsibleParty, 'Owner', pa.materialbase ) +
decode(pa.responsibleParty, 'Owner', pa.handlingbase ) +
decode(pa.responsibleParty, 'Owner', pa.taxbase ) )
as "Owner Total",
sum(
decode(pa.responsibleParty, 'User', pa.laborbase ) +
decode(pa.responsibleParty, 'User', pa.materialbase ) +
decode(pa.responsibleParty, 'User', pa.handlingbase ) +
decode(pa.responsibleParty, 'User', pa.taxbase ))
as "User Total",
rev.totalbase as "Grand Total"
from equipment E,
repairestimate ES,
estimaterevision REV,
facilitysegment FS,
equipmentEvent EE,
partyAllocation PA,
repair REP,
equipmentcode EC
where es.facilitysegmentid = fs.facilitysegmentid
and e.equipmentnumber = es.equipmentnumber
and ee.facilitysegmentid = fs.facilitysegmentid
and ee.eventtype = 'estimate'
and ee.iscurrent = 1
and rep.currentstate = 'REPAIR_COMPLETE'
and rep.repairstate = 'COMPLETE'
and ee.transactionNumber = es.transactionNUmber
and es.transactionNumber = rep.transactionNumber
and ec.equipmentcode(+) = e.equipmentcode
and rev.estimaterevisionid = es.currentestimaterevisionid
and pa.estimaterevisionid = rev.estimaterevisionid
and pa.responsibleParty in ( 'Owner', 'User' )
and pa.laborbase is not null
and pa.materialbase is not null
and rep.facilitysegmentid = es.facilitysegmentid
and rep.localTimeRepairCompleted between '01-jan-03' and to_date('01-may-03', 'Dd-Mon-YY')+1

Report message to a moderator

Re: Group By / Order By [message #7590 is a reply to message #7587]

Mon, 23 June 2003 17:53

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

The usage of aggregate functions like sum requires the use of a group by clause. Everything column that is selected, but not included in the aggregate function must be included in the group by clause. Then you can order by whatever you want (you haven't provided enough information or sample data to figure out what you want to order by). I would also use an explicit conversion for your date, in case someone changes the nls_date_format. So, your query should be something like:

select    fs.facilitycode                          as "Facility Code",
          rep.localTimeRepairCompleted             as "Repair Complete Date",
          pa.responsibleParty                      as "Responsible Party",
          es.baseCurrency                          as "Base Currency",
          sum (decode (pa.responsibleParty, 
                       'Owner', pa.laborbase ) +
               decode (pa.responsibleParty, 
                       'Owner', pa.materialbase ) +
               decode (pa.responsibleParty, 
                       'Owner', pa.handlingbase ) + 
               decode (pa.responsibleParty, 
                       'Owner', pa.taxbase ) )      as "Owner Total",
          sum (decode (pa.responsibleParty, 
                       'User', pa.laborbase ) +
               decode (pa.responsibleParty, 
                       'User', pa.materialbase ) + 
               decode (pa.responsibleParty, 
                       'User', pa.handlingbase ) +
               decode (pa.responsibleParty, 
                       'User', pa.taxbase ))        as "User Total",
          rev.totalbase                             as "Grand Total"
from      equipment        E,
          repairestimate   ES,
          estimaterevision REV,
          facilitysegment  FS,
          equipmentEvent   EE, 
          partyAllocation  PA,
          repair           REP,
          equipmentcode    EC
where     es.facilitysegmentid   =  fs.facilitysegmentid
and       e.equipmentnumber      =  es.equipmentnumber
and       ee.facilitysegmentid   =  fs.facilitysegmentid
and       ee.eventtype           =  'estimate'
and       ee.iscurrent           =  1
and       rep.currentstate       =  'REPAIR_COMPLETE'
and       rep.repairstate        =  'COMPLETE'
and       ee.transactionNumber   =  es.transactionNUmber
and       es.transactionNumber   =  rep.transactionNumber
and       ec.equipmentcode (+)   =  e.equipmentcode
and       rev.estimaterevisionid =  es.currentestimaterevisionid 
and       pa.estimaterevisionid  =  rev.estimaterevisionid
and       pa.responsibleParty    in ('Owner', 'User')
and       pa.laborbase           is not null
and       pa.materialbase        is not null 
and       rep.facilitysegmentid  =  es.facilitysegmentid
and       rep.localTimeRepairCompleted 
          between to_date ('01-jan-03', 'Dd-Mon-YY') 
          and     to_date ('01-may-03', 'Dd-Mon-YY') + 1
<b>group by  fs.facilitycode,
          rep.localTimeRepairCompleted,
          pa.responsibleParty,
          es.baseCurrency,
          rev.totalbase</b>
-- order by .....
/

Report message to a moderator

Previous Topic:	Partition Join
Next Topic:	QUERY.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:47:01 CDT 2024