[SQL] Calculate the ratio
Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode. Then i can see the ratio of the area of each country affected by cf. Any suggestions? Thanks. A.
Re: [SQL] Calculate the ratio
Hi, Try this select gridyear, gwcode, sum(case when cf=1 then area else o end) as cf1, sum(case when cf=0 then area else 0 end) as cf0 from table group by gridyear, gwcode http://sql-ex.ru/help/select8.php#case 15.08.2011, 16:21, "Andreas Forø Tollefsen" : > Hi all, > > This is probably quite simple. I want to calculate the ratio of each country > (gwcode) which experiences a 1 in one column (cf). > > Structure: > > gid; gridyear; gwcode; area; cf > > I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) > WHERE cf = 0 into another column and group by gridyear and gwcode. > Then i can see the ratio of the area of each country affected by cf. > > Any suggestions? > > Thanks. > > A. -- 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] Calculate the ratio
SELECT cols, SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Forø Tollefsen Sent: Monday, August 15, 2011 8:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] Calculate the ratio Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode. Then i can see the ratio of the area of each country affected by cf. Any suggestions? Thanks. A.
Re: [SQL] Calculate the ratio
Replace the 1 in the case with area like the msi77 said From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of David Johnston Sent: Monday, August 15, 2011 9:08 AM To: 'Andreas Forø Tollefsen'; pgsql-sql@postgresql.org Subject: Re: [SQL] Calculate the ratio SELECT cols, SUM( CASE WHEN cf = 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf = 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of Andreas Forø Tollefsen Sent: Monday, August 15, 2011 8:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] Calculate the ratio Hi all, This is probably quite simple. I want to calculate the ratio of each country (gwcode) which experiences a 1 in one column (cf). Structure: gid; gridyear; gwcode; area; cf I want to select the SUM(area) WHERE cf = 1 into one column, and SUM(area) WHERE cf = 0 into another column and group by gridyear and gwcode. Then i can see the ratio of the area of each country affected by cf. Any suggestions? Thanks. A.
Re: [SQL] pgadmin debugger
On Sunday, August 14, 2011 11:33:13 am David Harel wrote: > On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote: > > On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > > > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > > > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > > > > Greetings, > > > > > > > > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also > > > > > I have pgadmin version 1.10.2. I can't find debugger_plugin.so > > > > > which is needed to debug pgplsql using pgadmin: > > > > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PL > > > > > PgSQ L-De bugger.html > > > > > > > > > > Any idea? > > > > > > > > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html > > > > > > Trying to compile (no configure script found) the thing I get: > > > pldebugger # make > > > Makefile:42: ../../src/Makefile.global: No such file or directory > > > Makefile:43: /contrib/contrib-global.mk: No such file or directory > > > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. > > > > Looking at README.pldebugger indicates you need the development code for > > Postgres. Is your Postgres installation from the Ubuntu packages or did > > you compile it yourself? > > I installed form Ubuntu package. I am currently trying to install > postgresql-server-dev-8.4.8-0ubuntu0.10.04 package. > Will that suffice? Probably would not hurt to install the contrib package also. I generally install from source, so I am wandering into relatively unknown territory here:) -- 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
Re: [SQL] Calculate the ratio
Great. This works like I wanted. Thanks!
Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote: > Hi and thanks, > If my 'time' column is being saved as bigint: 128732389900. > How do I write a query to check if the 'time' field is greater than now - > 30 (past 30 days)? So what you want is not what values are greater than some point 30 days ago which is what your previous query asked and answered, but the values between a point 30 days ago and today. The easiest way is to use BETWEEN: test(5432)aklaver=>\d big_int_test Table "public.big_int_test" Column | Type | Modifiers +-+--- bint | bigint | rint | integer | test(5432)aklaver=>SELECT * from big_int_test ; bint |rint --+ 128732389900 | 1310799600 test(5432)aklaver=>SELECT bint FROM big_int_test WHERE bint BETWEEN extract(epoch from (date 'now' - integer '30')) AND extract(epoch from (date 'now')); bint -- (0 rows) That being said, if your time values are the order of magnitude shown they will not meet the criteria above. Is the time value supposed to be seconds? > > Thanks, > Janiv,. -- 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
Re: [SQL] bigint and unix time
Hi and thanks, If my 'time' column is being saved as bigint: 128732389900. How do I write a query to check if the 'time' field is greater than now - 30 (past 30 days)? Thanks, Janiv,. -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Sunday, August 14, 2011 20:57 To: pgsql-sql@postgresql.org Cc: Janiv Ratson Subject: Re: [SQL] bigint and unix time On Sunday, August 14, 2011 4:13:30 am Janiv Ratson wrote: > Hi, > > > > I have the following query: > > > > select ticket as ticket, time as created, author as reporter, > cast(extract(epoch from (date 'now' - integer '30')) as bigint) > > from ticket_change tc > > where field = 'status' > > and newvalue = 'reopened' > > and time > extract(epoch from (date 'now' - integer '30')) > > order by time > > > > I'm trying it get all records that their 'time' is within the past 30 > days. > > However, the time is bigint: 128732389900 > > While the extract(epoch from (date 'now' - integer '30')) is 1310677200 Bigint versus integer refers to the max values that the field can contain. For a given value of integer the storage should be the same for each up to the limit of the integer field. Would seem that whatever is putting values into time is inflating the values if they are actually referring to contemporary time values. > > > > As you understand, I always get all records ... > > > > How do I solve it? > > > > Thanks, > > Janiv. > -- 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
Re: [SQL] pgadmin debugger
On Mon, 2011-08-15 at 06:49 -0700, Adrian Klaver wrote: > On Sunday, August 14, 2011 11:33:13 am David Harel wrote: > > On Sun, 2011-08-14 at 11:07 -0700, Adrian Klaver wrote: > > > On Sunday, August 14, 2011 10:15:43 am David Harel wrote: > > > > On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: > > > > > On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: > > > > > > Greetings, > > > > > > > > > > > > I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also > > > > > > I have pgadmin version 1.10.2. I can't find debugger_plugin.so > > > > > > which is needed to debug pgplsql using pgadmin: > > > > > > http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PL > > > > > > PgSQ L-De bugger.html > > > > > > > > > > > > Any idea? > > > > > > > > > > http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html > > > > > > > > Trying to compile (no configure script found) the thing I get: > > > > pldebugger # make > > > > Makefile:42: ../../src/Makefile.global: No such file or directory > > > > Makefile:43: /contrib/contrib-global.mk: No such file or directory > > > > make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. > > > > > > Looking at README.pldebugger indicates you need the development code for > > > Postgres. Is your Postgres installation from the Ubuntu packages or did > > > you compile it yourself? > > > > I installed form Ubuntu package. I am currently trying to install > > postgresql-server-dev-8.4.8-0ubuntu0.10.04 package. > > Will that suffice? > > Probably would not hurt to install the contrib package also. I generally > install from source, so I am wandering into relatively unknown territory > here:) > Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] F_TABLE_NUMBER_RANGE equivalent in Postgresql
Hi, With MS-SQL sever, there is a built-in utility function dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the given range. For example: select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return: number --- 1 2 3 4 5 This is extremely convenient for generating consecutive dates. For example, I want to find out the daily sales information and if on some day there is no sales, the query should return 0 instead of a missing date. In this case it is necessary to first create a list of consecutive dates and then left join to some fact table. With the range function I can simply write something like select start_date + interval number from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days) How does one accomplish such task with Postgresql? -- Hong Yuan 大管家网上建材超市 装修装潢建材一站式购物 http://www.homemaster.cn -- 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] F_TABLE_NUMBER_RANGE equivalent in Postgresql
Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server? I suppose that this is UDF written by user. 16.08.2011, 08:53, "Yuan HOng" : > Hi, > > With MS-SQL sever, there is a built-in utility function > dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the > given range. For example: > > select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return: > > number > --- > 1 > 2 > 3 > 4 > 5 > > This is extremely convenient for generating consecutive dates. For > example, I want to find out the daily sales information and if on some > day there is no sales, the query should return 0 instead of a missing > date. > > In this case it is necessary to first create a list of consecutive > dates and then left join to some fact table. > > With the range function I can simply write something like > > select start_date + interval number > from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days) > > How does one accomplish such task with Postgresql? > > -- > Hong Yuan > > 大管家网上建材超市 > 装修装潢建材一站式购物 > http://www.homemaster.cn > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- 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] F_TABLE_NUMBER_RANGE equivalent in Postgresql
Hello this is not built in MSSQL, but PostgreSQL has a "generate_series" function Regards Pavel Stehule 2011/8/16 msi77 : > > Are you sure that dbo.F_TABLE_NUMBER_RANGE is in SQL Server? > I suppose that this is UDF written by user. > > > 16.08.2011, 08:53, "Yuan HOng" : >> Hi, >> >> With MS-SQL sever, there is a built-in utility function >> dbo.F_TABLE_NUMBER_RANGE which generates a list of number between the >> given range. For example: >> >> select dbo.F_TABLE_NUMBER_RANGE(1, 5) will return: >> >> number >> --- >> 1 >> 2 >> 3 >> 4 >> 5 >> >> This is extremely convenient for generating consecutive dates. For >> example, I want to find out the daily sales information and if on some >> day there is no sales, the query should return 0 instead of a missing >> date. >> >> In this case it is necessary to first create a list of consecutive >> dates and then left join to some fact table. >> >> With the range function I can simply write something like >> >> select start_date + interval number >> from dbo.F_TABLE_NUMBER_RANGE(1, number_of_days) >> >> How does one accomplish such task with Postgresql? >> >> -- >> Hong Yuan >> >> 大管家网上建材超市 >> 装修装潢建材一站式购物 >> http://www.homemaster.cn >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
