[GENERAL] Complex case statement
Hi All, I have three tables. Table: transaction meter_id | bay_number | trans_date_time | amount --+++ 1078 | 5 | 2013-06-03 09:59:32+10 | 5.00 1078 | 7 | 2013-06-03 09:12:01+10 | 5.00 1078 | 6 | 2013-06-03 08:33:11+10 | 5.00 1078 | 8 | 2013-05-27 09:48:26+10 | 5.00 1078 | 5 | 2013-05-27 09:41:42+10 | 5.00 1078 | 4 | 2013-05-27 09:41:08+10 | 5.00 1078 | 7 | 2013-05-20 17:14:15+10 | 2.00 1078 | 2 | 2013-05-20 16:19:10+10 | 2.50 Table: service meter_id | notified_at | finished_at --++ 1078 | 2013-05-30 15:02:27+10 | 2013-05-30 15:32:20+10 1078 | 2013-05-30 12:32:43+10 | 2013-05-30 14:50:14+10 1078 | 2013-05-30 08:31:27+10 | 2013-05-30 10:25:56+10 1078 | 2013-05-29 07:36:31+10 | 2013-05-29 11:35:47+10 Table: relocated_meter relocation_date | meter_id | bay_number | source_meter_id | source_bay_number -+--++-+--- 2013-04-24 | 1078 | 1 |1078 | 1 2013-04-24 | 1078 | 2 |1078 | 2 2013-04-24 | 1078 | 3 |1078 | 3 2013-04-24 | 1078 | 4 |1078 | 4 2013-04-24 | 1078 | 5 |1078 | 5 2013-04-24 | 1078 | 6 |1078 | 6 2013-04-24 | 1078 | 7 |1078 | 7 2013-04-24 | 1078 | 8 |1067 | 5 2013-04-24 | 1078 | 9 |1067 | 6 A quick sum(amount) where service.notified_at and service.finished_at gives me the amount received during service. To get the average amount between service.notified_at and service.finished_at for the last 52 weeks, I just run a case statement similar to the following: SUM (CASE WHEN meter_id IN (1078) AND trans_date_time BETWEEN 'notified_at' AND 'finished_at' THEN amount ELSE 0 END) AS 'week_no' I then sum the amount for each 'week_no'/52 to get the average yearly amount. Now, a few meters have inherited some bays and with the introduction on my third table called relocated_meter, I'd like to get the weekly amount PLUS income for another meter (1067 bay 5 and 1067 bay 6) because they're now part of meter 1078 bay 8 and 9, so average income should include the other bays too. I hope my question makes sense. Please advise if you like further details. Postgres version: Postgresql 9.2 OS: RHEL 5 Thanks!
[GENERAL] Finding duplicates only.
Hi, Can someone please help me with this duplicate query. I'm trying to: 1. Return duplicates only. (without including the first valid record), and 2. Return as duplicate if the difference between a.inspection_time and b.inspection time is under 5 minutes. Here's the query string I'm using to retrieve the duplicates but it is returning every duplicate records. select a.rego, a.inspection_date, a.inspection_time, count(*) as c from inspections a where exists ( select null from inspections b where a.rego = b.rego and a.inspection_date = b.inspection_date and a.inspection_time = b.inspection_time group by b.rego, b.inspection_date, b.inspection_time ) and status_id in (0) group by a.rego, a.inspection_date, a.inspection_time having count(*) > 1 Thanks in advance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] left join count
On 12 February 2010 10:28, Richard Huxton wrote: > On 11/02/10 22:53, Greenhorn wrote: >> >> But I seem to be getting (after the join) the sum of amount x count of >> notes. Can someone enlighten me with this problem? > >> select >> energy_accounts_id, count(note) >> ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 >> then t.total_amount else 0 end) as amount_current >> ,sum(case when t.fin_year = 2009 and t.fin_quarter = 1 >> then t.total_amount else 0 end) as amount_last >> from energy_transactions t >> left join energy_notes n on (t.id = n.energy_transactions_id) >> group by energy_accounts_id, total_amount > > If you were to eliminate the group by and aggregates you would see one row > for each match either side of the join. So - if t.id=123 had three notes > then it would be repeated three times, with the details of each note. As a > result, so is t.total_amount repeated three times. When you > sum(t.total_amount) you will get three times the value you expected. > > How to solve this? Split the two parts of the query and join their results. > Something like: > > SELECT > t.energy_accounts_id, > sum(coalesce(nc.note_count,0)) as note_count, > sum(...) as amount_current, > sum(...) as amount_last > FROM > energy_transactions t > LEFT JOIN ( > SELECT energy_transactions_id AS id, count(*) AS note_count > FROM energy_notes > GROUP BY energy_transactions_id > ) AS nc > ON t.id = nc.id > GROUP BY ... > > > The idea is that the subquery contains only one row for each id on the other > side of the join. > > HTH > -- > Richard Huxton > Archonet Ltd > Thank you :) Solved! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] left join count
Hi All, I'm trying to retrieve the count of notes associated for each transactions for table energy_transactions. But I seem to be getting (after the join) the sum of amount x count of notes. Can someone enlighten me with this problem? Here's the query that I am working with. select energy_accounts_id, count(note) ,sum(case when t.fin_year = 2010 and t.fin_quarter = 1 then t.total_amount else 0 end) as amount_current ,sum(case when t.fin_year = 2009 and t.fin_quarter = 1 then t.total_amount else 0 end) as amount_last from energy_transactions t left join energy_notes n on (t.id = n.energy_transactions_id) group by energy_accounts_id, total_amount Thanks in advance :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cross-database time extract?
2009/12/29 Israel Brewster : > > On Dec 24, 2009, at 12:53 PM, Adrian Klaver wrote: > >> On Thursday 24 December 2009 1:44:58 pm Israel Brewster wrote: >>> >>> This is sort of a PostgreSQL question/sort of a general SQL question, >>> so I apologize if this isn't the best place to ask. At any rate, I >>> know in PostgreSQL you can issue a command like 'SELECT >>> "time"(timestamp_column) from table_name' to get the time part of a >>> timestamp. The problem is that this command for some reason requires >>> quotes around the "time" function name, which breaks the command when >>> used in SQLite (I don't know about MySQL yet, but I suspect the same >>> would be true there). The program I am working on is designed to work >>> with all three types of databases (SQLite, PostgreSQL, and MySQL) so >>> it would be nice (save me some programing) if there was a single SQL >>> statement to get the time portion of a timestamp that would work with >>> all three. Is there such a beast? On a related note, why do we need >>> the quotes around "time" for the function to work in PostgreSQL? the >>> date function doesn't need them, so I know it's not just a general >>> PostgreSQL formating difference. Thanks :) >>> --- >>> Israel Brewster >>> Computer Support Technician II >>> Frontier Flying Service Inc. >>> 5245 Airport Industrial Rd >>> Fairbanks, AK 99709 >>> (907) 450-7250 x293 >>> --- >> >> select cast(timestamp_column as time) from table_name > you could try select timestamp_column::time from table_name -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Concurrency issue under very heay loads
2009/7/16 Raji Sridar (raji) : > Hi, > > We use a typical counter within a transaction to generate order sequence > number and update the next sequence number. This is a simple next counter - > nothing fancy about it. When multiple clients are concurrently accessing > this table and updating it, under extermely heavy loads in the system > (stress testing), we find that the same order number is being generated for > multiple clients. Could this be a bug? Is there a workaround? Please let me > know. > > Thanks > Raji You'll not have this problem if you use serial type. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] bash & postgres
Hi, I'm trying to pass variables on a bash script embedded with psql commands. cat header.txt "to1","from1","subject1" "to2","from2","subject2" "to3","from3","subject3" "to4","from4","subject4" cat b.sh #!/bin/bash two="2" psql -h localhost -U postgres -d mobile -c "create temp table header ( field_1 textnot null, field_2 textnot null, field_3 textnot null ); \\copy header FROM header.txt CSV SELECT * FROM header limit "$two"; " When I execute b.sh ERROR: syntax error at or near "\" LINE 10: \copy header FROM header.txt CSV ^ How do I use \c (or any other psql commands beginning with a "\") in a bash script? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Oracle to PostgreSQL
Hi, I have almost 1300 files from Oracle (no access to oracle server). I have to create the tables and data as follows. -- start script -- file name: aa_asset_type.sql CREATE TABLE AS_ASSET_TYPE ( MAIN_TYPE CHAR(3) NOT NULL, SUB_TYPE NUMBER(3) NOT NULL, DESCRIPTION VARCHAR2(25)DEFAULT ' ' NOT NULL, SERVICE_SCHEDULE VARCHAR2(8) DEFAULT ' ' NOT NULL, PRODUCT_CODE CHAR(4) DEFAULT ' ' NOT NULL ); GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON GENADMIN.AS_ASSET_TYPE TO PUBLIC; INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE, PRODUCT_CODE ) VALUES ( '000', 0, ' ', ' ', ''); INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE, PRODUCT_CODE ) VALUES ( '001', 0, ' ', ' ', ''); INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE, PRODUCT_CODE ) VALUES ( '100', 0, 'BUILDINGS', ' ', ''); -- file name: as_master.sql CREATE TABLE AS_MASTER ( ASSET_NO VARCHAR2(16) DEFAULT ' ' NOT NULL, FUND CHAR(1) NOT NULL, FINAL_ACCOUNT_NO NUMBER(6)NOT NULL, ITEM_NO NUMBER(6)NOT NULL, SUB_NO NUMBER(6)NOT NULL, TYPE NUMBER(6)DEFAULT 0 NOT NULL, SUB_TYPE NUMBER(6)DEFAULT 0 NOT NULL, LOCATION NUMBER(6)DEFAULT 0 NOT NULL, SUB_LOCATION NUMBER(6)DEFAULT 0 NOT NULL, LEVEL_A VARCHAR2(4) DEFAULT ' ' NOT NULL, LEVEL_B VARCHAR2(4) DEFAULT ' ' NOT NULL, LEVEL_C VARCHAR2(4) DEFAULT ' ' NOT NULL, LEVEL_D VARCHAR2(4) DEFAULT ' ' NOT NULL, LEVEL_E VARCHAR2(4) DEFAULT ' ' NOT NULL, OPENING_ACQ_VALUENUMBER(15,2) DEFAULT 0 NOT NULL, OPENING_ACQ_QTY NUMBER(12) DEFAULT 0 NOT NULL, WITHDRAWAL_VALUE NUMBER(15,2) DEFAULT 0 NOT NULL, WITHDRAWAL_QTY NUMBER(12) DEFAULT 0 NOT NULL, PROFIT_LOSS_VALUENUMBER(15,2) DEFAULT 0 NOT NULL, DEPREC_VALUE NUMBER(15,2) DEFAULT 0 NOT NULL, PROFIT_LOSS_YTD NUMBER(15,2) DEFAULT 0 NOT NULL, DEPREC_YTD NUMBER(15,2) DEFAULT 0 NOT NULL, DEPREC_LAST_MONTHNUMBER(6)DEFAULT 0 NOT NULL, DEPREC_LAST_YEAR NUMBER(6)DEFAULT 0 NOT NULL, DEPREC_METHODCHAR(1) DEFAULT ' ' NOT NULL, DEPREC_RATE_2NUMBER(15,6) DEFAULT 0 NOT NULL, DEPREC_ROUND_FACTOR NUMBER(6)DEFAULT 0 NOT NULL, DEPREC_FREQUENCY CHAR(1) DEFAULT ' ' NOT NULL, DEPREC_MINIMUM_VAL NUMBER(12) DEFAULT 0 NOT NULL, DEPREC_TYPE NUMBER(6)DEFAULT 0 NOT NULL, DEPREC_FOLIO_DR VARCHAR2(8) DEFAULT ' ' NOT NULL, DEPREC_FOLIO_CR VARCHAR2(8) DEFAULT ' ' NOT NULL, INSURANCE_VALUE NUMBER(12) DEFAULT 0 NOT NULL, INSURANCE_CATEGORY VARCHAR2(2) DEFAULT ' ' NOT NULL, INSURANCE_POLICY VARCHAR2(10) DEFAULT ' ' NOT NULL, INSURANCE_CODE CHAR(1) DEFAULT ' ' NOT NULL, ASSET_FILE_NOVARCHAR2(10) DEFAULT ' ' NOT NULL, PROPERTY_REF VARCHAR2(20) DEFAULT ' ' NOT NULL, LAST_AUDIT_DATE DATE NULL, LAST_AUDIT_PAGE NUMBER(6)DEFAULT 0 NOT NULL, DESCRIPTION_1VARCHAR2(40) DEFAULT ' ' NOT NULL, DESCRIPTION_2VARCHAR2(40) DEFAULT ' ' NOT NULL ); GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON GENADMIN.AS_MASTER TO PUBLIC; ALTER TABLE GENADMIN.AS_MASTER ADD ( PRIMARY KEY (ASSET_NO)); INSERT INTO AS_MASTER ( ASSET_NO, FUND, FINAL_ACCOUNT_NO, ITEM_NO, SUB_NO, TYPE, SUB_TYPE, LOCATION, SUB_LOCATION, LEVEL_A, LEVEL_B, LEVEL_C, LEVEL_D, LEVEL_E, OPENING_ACQ_VALUE, OPENING_ACQ_QTY, WITHDRAWAL_VALUE, WITHDRAWAL_QTY, PROFIT_LOSS_VALUE, DEPREC_VALUE, PROFIT_LOSS_YTD, DEPREC_YTD, DEPREC_LAST_MONTH, DEPREC_LAST_YEAR, DEPREC_METHOD, DEPREC_RATE_2, DEPREC_ROUND_FACTOR, DEPREC_FREQUENCY, DEPREC_MINIMUM_VAL, DEPREC_TYPE, DEPREC_FOLIO_DR, DEPREC_FOLIO_CR, INSURANCE_VALUE, INSURANCE_CATEGORY, INSURANCE_POLICY, INSURANCE_CODE, ASSET_FILE_NO, PROPERTY_REF, LAST_AUDIT_DATE, LAST_AUDIT_PAGE, DESCRIPTION_1, DE
