CODEWITHSUNDEEP
sqlpostgresql
user809829
user809829

Reputation: 1189

Get historical count and current count on parking data

I've previously got very good help here on SO in regards to analyze parking data. This is my query:

select parking_meter_id, avg(cnt) from
  (select parking_meter_id, count(*) as cnt, to_char(start,'YYYYMMDD') as day
  from parking_transactions
  where start >= now() - interval '3 month' -- last three months
  and to_char(start,'YYYYMMDD') < to_char(now(),'YYYYMMDD') -- but not today
  and to_char(start,'D') = to_char(now(),'D') -- same weekday
  and to_char(now(),'HH24MISS') between to_char(start,'HH24MISS') and to_char(stop,'HH24MISS') -- same time
  group by parking_meter_id, to_char(start,'YYYYMMDD') -- group by day
) as parking_transactions group by parking_meter_id

It does work and show average count on active transactions this is due to the fact that transactions from today (now()) are filtered away.

Is it possible, in same run through, to have the query also return the current active transactions:

select count(*) as cnt from parking_transactions where now() between start and stop

so one can easily compare the current status with the historical?

My table structure are:

parking_meter_id, start, stop

Currently I get the following output:

parking_meter_id, avg(cnt) minus today

I would like to have the following output:

parking_meter_id, avg(cnt) minus today, count(*) for today only

The -- but not today are the where clause which ignores todays transactions.

An example of output as of now is the following:

 parking_meter_id | cnt |   day    
------------------+-----+----------
             4406 |   1 | 20141217
             4406 |   5 | 20150107
             4406 |   1 | 20150121
             4406 |   3 | 20150128
             4406 |   3 | 20150114

I would like to have returned:

 parking_meter_id | avg(cnt-without-today) |   cnt-day    
------------------+-----+------------------------------
             4406 |   2.6                  | 3

Upvotes: 0

Views: 136

Answers (1)

Ram
Ram

Reputation: 3091

Use WITH to create temporary tables for daily count and avg count minus today and join the tables to get desired result

SQL Fiddle

SQL

WITH daily_count AS  -- temp table to store daily counts 
(
         SELECT   parking_meter_id,
                  COUNT(*)                  AS cnt,
                  to_char(start,'YYYYMMDD') AS day
         FROM     parking_transactions
         WHERE    start >= now() - interval '3 month' -- last three months
         AND      to_char(start,'D') = to_char(now(),'D') -- same weekday
         AND      to_char(now(),'HH24MISS') BETWEEN to_char(start,'HH24MISS') AND to_char(stop,'HH24MISS') -- same time
         GROUP BY parking_meter_id,
                  to_char(start,'YYYYMMDD') -- group by parking meter id and day
), avg_count_minus_today AS -- temp table to store avg count minus today 
(
         SELECT   parking_meter_id,
                  AVG(cnt) AS avg_count
         FROM     daily_count
         WHERE    day < to_char(now(),'YYYYMMDD') -- but not today
         GROUP BY parking_meter_id 
)
SELECT     a.parking_meter_id,
           a.avg_count, --avg count minus today
           d.cnt AS today_count
FROM       avg_count_minus_today a
INNER JOIN daily_count d
ON         a.parking_meter_id= d.parking_meter_id AND d.day=to_char(now(),'YYYYMMDD'); --today in daily count

Upvotes: 1

Related Questions