This is more a general sql question than postgresql related but it is on
a postgres server so ....
Following table
a_table id(serial), shift_date(current_date), shift_name(varchar),
stop_time(int),run_time(int) (stop and run are in minutes), machine_no(imt)
1 2009/08/07 A 719 1 2
2 2009/08/07 A 719 1 5
3 2009/08/07 A 719 1 6
4 2009/08/07 A 719 1 9
5 2009/08/07 A 719 1 10
...
67 2009/08/07 B 1 719 2
68 2009/08/07 B 102 618 5
69 2009/08/07 B 719 1 6
70 2009/08/07 B 12 708 9
71 2009/08/07 B 2 718 10
.....
167 2009/08/08 A 0 720 2
168 2009/08/08 A 1 719 5
169 2009/08/08 A 5 715 6
170 2009/08/08 A 720 0 9
171 2009/08/08 A 1 719 10
etc
OK my problem ,
The table is populated by a third party text file that monitors
machine run/stop times. If none of the machines run at all on a shift I
want to exclude them (eg above table 1-5 exclude but include all the
rest including 69,170).
What i want is something like
SELECT machine_no, sum(stop_time, sum(run_time) FROM a_table where **
(SELECT sum(run_time) from a_table group by shift_name,shift_date) ** >
67 group by machine_no;
(the > 67 is based on the fact that it usually monitors 40-80 machines
currently 66)
so I end up with
2 1 1439
5 103 1337
....
Not
2 720 1440
5 822 1338
...
Any help or pointers to rtfm/docs/webpages be appreciated
Peter Jackson
