Re: counting query

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/28/07 07:05, garry saddington wrote:
> I have a table definition such as:
>
> CREATE TABLE attendance
> (
> attendanceid serial primary key,
> entered date DEFAULT current_date NOT NULL,
> absent boolean,
> authorization text default 'N',
> timeperiod char(2) check(timeperiod in('AM','PM')),
> days varchar(10),
> studentid int,
> unique(entered,timeperiod,studentid)
> )
>
> Which is used to record school attendance data. I am now trying to write
> a query to identify trends in absences by counting the days column and
> returning any student that has repeated absences on certain days. I am
> struggling to return anything that does not need further manipulation in
> Python before being useful.
> Does anyone have any ideas?

When you say "certain days", you mean "days of the week"?

If so, create a view like:
CREATE VIEW V_DAY_ABSENCES AS
SELECT ENTERED,
AUTHORIZATION,
TIMEPERIOD,
DAYS,
STUDENTID,
DOW(CAST(ENTERED AS TIMESTAMP)) AS WEEKDAY
FROM ATTENDANCE
WHERE ABSENT = TRUE;

Then, this query should do what you want:
SELECT STUDENTID,
TIMEPERIOD,
WEEKDAY,
COUNT(*)
FROM V_DAY_ABSENSES
GROUP BY STUDENTID,
TIMEPERIOD,
WEEKDAY
HAVING COUNT(*) > 3;

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFvMgCS9HxQb37XmcRAkvrAJ9ZiOJCM8GGE7ptIzcZsUJc7T2fnQCgpkUn
/9nkR9BO04WB0XThPlx+254=
=9D2A
-----END PGP SIGNATURE-----