Re: [SQL] group by with sum and sum till max date
On Tue, Jul 5, 2011 at 10:42 AM, M. D. wrote: > This is a little hard to explain, and I'm not sure if it's possible, but > here goes. > > This is my query: > select year, month, > (select number from account where account.account_id = > view_account_change.account_**id) as number, > (select name from account where account.account_id = > view_account_change.account_**id) as account, > sum(amount) as amount > from view_account_change > where view_account_change.change_**date >= '2010-01-01' > group by year,month, number, account > order by year,month, number, account > > I want to make an exception for the sum so that if the account number is > less than 4000, I want a sum of all transactions until the last date of the > group by. > > the query for that would be: > Select sum(amount) from view_account_change where change_date > "max date > in the group" > I think you are looking for a window function, but I'm not sure about using a value computed over a window in the where clause. You may have to do something somewhat complicated with a subquery, but you can definitely compute 'max date in the group' via a window function: http://www.postgresql.org/docs/9.0/static/tutorial-window.html http://www.postgresql.org/docs/9.0/static/functions-window.html http://www.postgresql.org/docs/9.0/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS You'll like have to split it into a UNION of 2 queries, one for account numbers lower than 4000 and the other for the rest. Perhaps select max date in the group in a subquery which you then join to in an outer query. That should be enough to start experimenting with, anyway.
Re: [SQL] interesting sequence
On 2011-07-06, Kevin Crain wrote: > That's why you need to do this inside a function. Basically just make > an insert function for the table and have it calculate the count and > do the insert in one transaction. you will still get duplicates, so include code in the function to retry if there is an error. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] interesting sequence
On 06/07/11 01:52, John Fabiani wrote: Hi, I have a special need to create a sequence like function. "O-20110704 -2" which is "O" for order (there are other types) "20110704" is for July 4, 2011 '2' the second order of the day for July 4, 2011 I of course can get the type and date. What I don't know is how to get is the last number. It would seem to be that I would need a loop to determine if the next number existed. LOOP --Check to see if the string exist in a table -- count = count +1 -- until I don't find the string END LOOP; but then I thought I could do something like for $1 in (select string from sometable) LOOP count = count + 1 or something like this for i in 1..999 LOOP -- check for the existence of the string in a table using 'i' -- there will never be 999 orders in one day. END LOOP So here is the question what would be the best way for a multi-user system? If someone has a better thought - it would be helpful. BTW I did NOT design the number - in fact it seems silly to me. Johnf Hi John, How about using a table to hold the latest sequence for each order type and date, along with a function to insert a new order? (I've included the code to test the idea and the results, I am using 9.1beta2, but it should not make any difference - I think!): DROP TABLE IF EXISTS my_order; DROP TABLE IF EXISTS order_sequence; CREATE TABLE my_order ( order_num text PRIMARY KEY, payload text ); CREATE TABLE order_sequence ( typeint, day date, seq int NOT NULL, PRIMARY KEY (type, day) ); CREATE OR REPLACE FUNCTION create_my_order ( IN typeint, IN day date, IN payload text ) RETURNS VOID AS $$ DECLARE v_order_num text; v_seq_old int; v_seq_new int; BEGIN SELECT os.seq FROM order_sequence os WHERE os.type = create_my_order.type AND os.day = create_my_order.day INTO v_seq_old; IF v_seq_old IS NULL THEN v_seq_new := 1; INSERT INTO order_sequence(type, day, seq) VALUES (type, day, v_seq_new); ELSE v_seq_new := v_seq_old + 1; UPDATE order_sequence AS os SET seq = v_seq_new WHERE os.type = create_my_order.type AND os.day = create_my_order.day; END IF; v_order_num := type::text || '-' || to_char(day, 'YYMMDD') || '-' || v_seq_new::text; INSERT INTO my_order(order_num, payload) VALUES (v_order_num, payload); END; $$ LANGUAGE plpgsql VOLATILE ; SELECT create_my_order (0, '2010-03-24', 'order #1 details'); SELECT create_my_order (0, '2010-03-24', 'order #2 details'); SELECT create_my_order (0, '2010-06-15', 'order #3 details'); SELECT create_my_order (5, '2010-03-24', 'order #4 details'); SELECT create_my_order (0, '2010-06-15', 'order #5 details'); SELECT create_my_order (3, '2010-06-14', 'order #6 details'); TABLE order_sequence; TABLE my_order; // This outputs the following: type |day | seq --++- 0 | 2010-03-24 | 2 5 | 2010-03-24 | 1 0 | 2010-06-15 | 2 3 | 2010-06-14 | 1 (4 rows) order_num | payload +-- 0-100324-1 | order #1 details 0-100324-2 | order #2 details 0-100615-1 | order #3 details 5-100324-1 | order #4 details 0-100615-2 | order #5 details 3-100614-1 | order #6 details (6 rows)
[SQL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?
Good morning,
A question about: ERROR: malformed record literal: ""
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column| Type
-+
run_date| character varying(128)
adm_year| character varying(4)
adm_sess| character varying(1)
faculty | character varying(128)
ac_cycle| character varying(128)
deg_code| character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed| numeric
accepted| numeric
confirmed | numeric
registered | numeric
hold| numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;
Thank you,
Emi
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?
A question about: ERROR: malformed record literal: ""
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column | Type
-+
run_date | character varying(128)
adm_year | character varying(4)
adm_sess | character varying(1)
faculty | character varying(128)
ac_cycle | character varying(128)
deg_code | character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed | numeric
accepted | numeric
confirmed | numeric
registered | numeric
hold | numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;
I have fixed it.
It should not be z_drop, it should be real column names.
The mailing list email appears so slow :-( Only after 4 hours it show!
Emi
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;
Thank you,
Emi
--
Emi Lu, ENCS, Concordia University, Montreal H3G 1M8
em...@encs.concordia.ca+1 514 848-2424 x5884
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ERROR: malformed record literal: "",DETAIL: Missing left parenthesis?
On 07/06/2011 12:03 PM, Emi Lu wrote:
A question about: ERROR: malformed record literal: ""
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column | Type
-+
run_date | character varying(128)
adm_year | character varying(4)
adm_sess | character varying(1)
faculty | character varying(128)
ac_cycle | character varying(128)
deg_code | character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed | numeric
accepted | numeric
confirmed | numeric
registered | numeric
hold | numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;
I have fixed it.
It should not be z_drop, it should be real column names.
The mailing list email appears so slow :-( Only after 4 hours it show!
What happens if you do?:
PREPARE test AS INSERT
My rough guess is that z_drop is being applied to the first parameter only.
Emi
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;
Thank you,
Emi
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
