[SQL] postgres sql help
hi, i am fairly new in postgresql, so if anyone can help me would be great if i simply do: select ver_no from version order by ver_no the result will be something like this: .1.3.1 .1.3.2.5. .1.4.1.7.12 .1.4.11.14.7. .1.4.3.109.1. .1.4.8.66. so as you can see first 3 lines are ok, but how to make 1.4.3.109 come before 1.4.11 because the third level "3" is smaller than "11". i understand they are stored as char so i used split_part function to separate each numbers between "." in a separate column. but when i try to convert those column into integer, i am getting an error msg saying: ERROR: invalid input syntax for type numeric: " " here is my code: select ver_no, duedate, status, to_number(split_part(ver_no, '.', 2), '') a, to_number(split_part(ver_no, '.', 3), '') b, to_number(split_part(ver_no, '.', 4), '') c, to_number(split_part(ver_no, '.', 5), '') d from version order by a,b,c,d I am not sure if i am heading towards wrong direction but can someone please suggest or give me some other ideas to sort this. PS: i found some good solution in SQL Server but those commands are not used in postgreSQL. Thanks in advance - James
[SQL] Group by on Date
I have a table with Date and unit column. . I want to find sum of unit column considering 3 days each User will specify start and enddate Eg DateUnit 10/1/2011 1 10/2/2011 2 10/3/2011 3 10/4/2011 4 10/5/2011 4 10/6/2011 1 10/7/2011 2 10/8/2011 3 10/9/2011 1 10/10/20111 10/11/20111 suppose if user selects date 10/1/2011 to 10/6/2011 then output should be start date enddate unit 10/1/2011 10/3/2011 6 10/4/2011 10/6/20119 pls let me know if anybody has any idea. Thanks in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/Group-by-on-Date-tp4904685p4904685.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres sql help
James Bond wrote: > hi, i am fairly new in postgresql, so if anyone can help me would be great > > if i simply do: > > select ver_no > from version > order by ver_no > > the result will be something like this: > > .1.3.1 > .1.3.2.5. > .1.4.1.7.12 > .1.4.11.14.7. > .1.4.3.109.1. > .1.4.8.66. > > so as you can see first 3 lines are ok, but how to make 1.4.3.109 come before > 1.4.11 because the third level "3" is smaller than "11". i understand they are > stored as char so i used split_part function to separate each numbers between > "." in a separate column. but when i try to convert those column into integer, > i am getting an error msg saying: ERROR: invalid input syntax for type > numeric: > " " > > here is my code: test=*# select * from o; t --- .1.3.1 .1.3.2.5. .1.4.1.7.12 .1.4.11.14.7. .1.4.3.109.1. .1.4.8.66. (6 rows) Time: 0,262 ms test=*# select * from o order by split_part(t,'.',2)::int, split_part(t,'.',3)::int, split_part(t,'.',4)::int; t --- .1.3.1 .1.3.2.5. .1.4.1.7.12 .1.4.3.109.1. .1.4.8.66. .1.4.11.14.7. (6 rows) Time: 0,403 ms Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgres sql help
In article , James Bond writes: > hi, i am fairly new in postgresql, so if anyone can help me would be great > if i simply do: > select ver_no > from version > order by ver_no > the result will be something like this: > .1.3.1 > .1.3.2.5. > .1.4.1.7.12 > .1.4.11.14.7. > .1.4.3.109.1. > .1.4.8.66. > so as you can see first 3 lines are ok, but how to make 1.4.3.109 come > before 1.4.11 because the third level "3" is smaller than "11". The query SELECT ver_no FROM version ORDER BY string_to_array(ver_no, '.', '')::int[] should do what you want. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Group by on Date
maya.more wrote: > I have a table with Date and unit column. . I want to find sum of unit column > considering 3 days each > > User will specify start and enddate > > Eg > > DateUnit > 10/1/2011 1 > 10/2/2011 2 > 10/3/2011 3 > 10/4/2011 4 > 10/5/2011 4 > 10/6/2011 1 > 10/7/2011 2 > 10/8/2011 3 > 10/9/2011 1 > 10/10/20111 > 10/11/20111 > suppose if user selects date 10/1/2011 to 10/6/2011 > > then output should be > > start date enddate unit > 10/1/2011 10/3/2011 6 > 10/4/2011 10/6/20119 > > > pls let me know if anybody has any idea. Maybe something like that: test=*# select * from d; d | i + 2011-10-17 | 1 2011-10-18 | 2 2011-10-19 | 3 2011-10-20 | 4 2011-10-21 | 5 2011-10-22 | 6 2011-10-23 | 7 2011-10-24 | 8 2011-10-25 | 9 2011-10-26 | 10 2011-10-27 | 11 2011-10-28 | 12 2011-10-29 | 13 2011-10-30 | 14 2011-10-31 | 15 2011-11-01 | 16 2011-11-02 | 17 2011-11-03 | 18 2011-11-04 | 19 2011-11-05 | 20 (20 rows) Time: 0,276 ms test=*# select '2011-01-01'::date + (extract(doy from d) / 3)::int * '3day'::interval - '3day'::interval as "from", '2011-01-01'::date + (extract(doy from d) / 3)::int * '3day'::interval as "to", sum(i) from d group by 1,2 order by 1; from | to | sum -+-+- 2011-10-16 00:00:00 | 2011-10-19 00:00:00 | 6 2011-10-19 00:00:00 | 2011-10-22 00:00:00 | 15 2011-10-22 00:00:00 | 2011-10-25 00:00:00 | 24 2011-10-25 00:00:00 | 2011-10-28 00:00:00 | 33 2011-10-28 00:00:00 | 2011-10-31 00:00:00 | 42 2011-10-31 00:00:00 | 2011-11-03 00:00:00 | 51 2011-11-03 00:00:00 | 2011-11-06 00:00:00 | 39 (7 rows) it fails for more than 1 year, but i hope, you can modify my sql for your own. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Group by on Date
In article <[email protected]>, "maya.more" writes: > I have a table with Date and unit column. . I want to find sum of unit column > considering 3 days each > User will specify start and enddate > Eg > DateUnit > 10/1/2011 1 > 10/2/2011 2 > 10/3/2011 3 > 10/4/2011 4 > 10/5/2011 4 > 10/6/2011 1 > 10/7/2011 2 > 10/8/2011 3 > 10/9/2011 1 > 10/10/20111 > 10/11/20111 > suppose if user selects date 10/1/2011 to 10/6/2011 > then output should be > start date enddate unit > 10/1/2011 10/3/2011 6 > 10/4/2011 10/6/20119 SELECT ('2011-10-01'::date + INTERVAL '3 day' * ((date - '2011-10-01') / 3))::date AS start_date, ('2011-10-01'::date + INTERVAL '2 day' + INTERVAL '3 day' * ((date - '2011-10-01') / 3))::date AS end_date, sum(unit) AS unit FROM tbl WHERE date BETWEEN '2011-10-01' AND '2011-10-06' GROUP BY 1, 2 ORDER BY 1 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
