date_format, group by and order by together
793966Aug 27 2010 — edited Aug 31 2010Hi,
SELECT
T.foreign_network_id as "Network ID",
date_format(T.ARRIVAL_DATE,'%v-%x') as "Date",
sum(case when((networks_rejected = 0)
and (T.sor_reapply = 'N')
and (T.sor_reason in ('NP', 'NU', 'NT', 'HT')))
then T.CNT else 0 end) as "Before Steering",
sum(case when(sor_reason in ('NP', 'HT', 'T2', 'TM', 'NM', 'MM', 'IPMM', 'NPMM'))
then T.CNT else 0 end) as "After Steering"
FROM ST_INC T,
NETWORKS N,
ZONE_NW_MAP ZN,
ZONES Z,
PAYMENT_TYPE PT
WHERE T.FOREIGN_NETWORK_ID = N.NETWORK_ID
AND ZN.NETWORK_ID = N.NETWORK_ID
AND Z.ZONE_ID = ZN.ZONE_ID
AND PT.id = T.ptype
AND opcode = 2
AND sor_action is not null
and Z.ZONE_ID = 269
group by
date_format(T.ARRIVAL_DATE,'%v-%x'),
T.foreign_network_id
order by date_format(T.ARRIVAL_DATE,'%v-%x')
Above code works but it orders malfunctionally. It orders as if the week-day combination is a string not a date.
I want:
09.2009
10.2009
02.2010
03.2010
06.2011
It outputs:
02.2010
03.2010
06.2011
09.2009
10.2009
I know that the problem arises from the "order by" part. When I call the function date_format I guess a different data type is returnd thus the result is not achieved as intended. So I thought of changing that part only making last line order by T.ARRIVAL_DATE. It worked fine in mysql however it gave error in oracle. This is the error:
ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
When I replace all "date_format(T.ARRIVAL_DATE,'%v-%x')" s with "T.ARRIVAL_DATE" it works fine again. However this is not what i wanted. I want to see the results in the format I gave and group with respect to that fomat, additionally ordering them chronologically. How is that possible?