| Results by month by average [message #8153] |
Wed, 30 July 2003 12:13  |
Heidi
Messages: 37
Registered: February 2000
|
Member |
|
|
Okay, I admit I am over my head with this one and would appreciate some help. What I want are my results to look like this:
DATE AVG_GATEIN_PERDAY AVG_GATEOUT_PERDAY
Jan-03 15 25
Feb-03 25 12
Mar-03 10 6
Apr-03 25 15
May-03 40 30
Jun-03 50 60
But I am lost on how to get my data into rows (for the months) as well as how to average. I would so appreciate anyone's help. This is m query so far. I can provide any informatin anyone wants.
SELECT DISTINCT
EE.eventtype
, FS.facilitycode
, count(*)
FROM
equipmentevent EE
, facilitysegment FS
WHERE
EE.timestamp >= TO_DATE ('01-jan-2003' , 'DD-MON-YYYY')
AND EE.timestamp <= TO_DATE ('31-jun-2003' , 'DD-MON-YYYY')
AND EE.facilitysegmentid = FS.facilitysegmentid
AND EE.designatedowner = 'TRI'
AND FS.facilitycode = 'USEWRINTA'
AND EE.eventtype in ('gateIn', 'gateOut')
GROUP BY FS.facilitycode, EE.eventtype
ORDER BY FS.facilitycode, EE.eventtype
|
|
|
|
| Re: Results by month by average [message #8154 is a reply to message #8153] |
Wed, 30 July 2003 13:28  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Perhaps something like:
select trunc(ee.timestamp, 'mm') event_date,
count(decode(ee.eventtype, 'gateIn', 1)) gateInCount,
count(decode(ee.eventtype, 'gateOut', 1)) gateOutCount
from equipmentevent ee,
facilitysegment fs
where ee.timestamp >= to_date ('01-jan-2003', 'DD-MON-YYYY')
and ee.timestamp <= to_date ('31-jun-2003', 'DD-MON-YYYY')
and ee.eventtype in ('gateIn', 'gateOut')
and ee.designatedowner = 'TRI'
and ee.facilitysegmentid = fs.facilitysegmentid
and fs.facilitycode = 'USEWRINTA'
group by trunc(ee.timestamp, 'mm')
order by trunc(ee.timestamp, 'mm');
Your subject mentioned averaging, but your SQL showed counts, so I went with counts. If you really want averaging, you'll have to tell us what is being averaged.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
