Help sub query
От | Peter Jackson |
---|---|
Тема | Help sub query |
Дата | |
Msg-id | 4A7FEEB8.7030204@tasjackson.com обсуждение исходный текст |
Список | 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
В списке pgsql-novice по дате отправления: