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
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 |