Skip site navigation (1) Skip section navigation (2)

Re: Cross tabulations

From: Greg Stark <gsstark(at)mit(dot)edu>
To: "Muhyiddin A(dot)M Hayat" <middink(at)indo(dot)net(dot)id>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Cross tabulations
Date: 2004-10-20 06:35:25
Message-ID: 878ya128ya.fsf@stark.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-sql
"Muhyiddin A.M Hayat" <middink(at)indo(dot)net(dot)id> writes:

> Dear all,
> 
> I need to do something similar to a cross tabulation, but without any
> aggregation.

join your table to itself four times:

select * 
  from (select check_time::date as date, employee_id, check_time-check_time::date as in from test where state = 'In') as a 
  join (select check_time::date as date, employee_id, check_time-check_time::date as break_out from test where state = 'Break Out') as b using (employee_id,date)
  join (select check_time::date as date, employee_id, check_time-check_time::date as break_in from test where state = 'Break In') as d using (employee_id,date)
  join (select check_time::date as date, employee_id, check_time-check_time::date as out from test where state = 'Out') as e using (employee_id,date) ;

Note that this will do strange things if you don't have precisely four records
for each employee.

Alternatively use subqueries:

select date, employee_id,
       (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'In') as in,
       (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Break Out') as break_out,
       (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Break In') as break_in,
       (select check_time-check_time::date from test where employee_id = x.employee_id and check_time::date = date and state = 'Out') as out
  from (select distinct employee_id, check_time::date as date from test) as x; 

This will at least behave fine if there are missing records and will give an
error if there are multiple records instead of doing strange things.

Neither of these will be particularly pretty on the performance front.

-- 
greg


In response to

Responses

pgsql-sql by date

Next:From: Kathiravan VelusamyDate: 2004-10-20 07:03:59
Subject: SQL update function faililed in Webmin Interface
Previous:From: Muhyiddin A.M HayatDate: 2004-10-20 04:43:00
Subject: Cross tabulations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group