Help sub query

From: Peter Jackson <mltasmaniac(at)tasjackson(dot)com>
To: pgsql <pgsql-novice(at)postgresql(dot)org>
Subject: Help sub query
Date: 2009-08-10 09:56:08
Message-ID: 4A7FEEB8.7030204@tasjackson.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Lush 2009-08-11 15:19:27 Moving filesystems
Previous Message Andreas Wenk 2009-08-09 20:35:03 Re: psql o and g options not working