Calculate the ratio

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

Calculate the ratio
5+ messages / 3 participants
[nested] [flat]

* Calculate the ratio
@ 2011-08-15 12:21  Andreas Forø Tollefsen <andreasft@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Andreas Forø Tollefsen @ 2011-08-15 12:21 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Calculate the ratio
@ 2011-08-15 13:00  msi77 <msi77@yandex.ru>
  parent: Andreas Forø Tollefsen <andreasft@gmail.com>
  1 sibling, 0 replies; 5+ messages in thread

From: msi77 @ 2011-08-15 13:00 UTC (permalink / raw)
  To: pgsql-sql

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" <andreasft@gmail.com>:
> 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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Calculate the ratio
@ 2011-08-15 13:08  David Johnston <polobo@yahoo.com>
  parent: Andreas Forø Tollefsen <andreasft@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: David Johnston @ 2011-08-15 13:08 UTC (permalink / raw)
  To: 'Andreas Forø Tollefsen' <andreasft@gmail.com>; pgsql-sql

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-owner@postgresql.org [mailto:pgsql-sql-owner@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.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Calculate the ratio
@ 2011-08-15 13:10  David Johnston <polobo@yahoo.com>
  parent: David Johnston <polobo@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: David Johnston @ 2011-08-15 13:10 UTC (permalink / raw)
  To: 'Andreas Forø Tollefsen' <andreasft@gmail.com>; pgsql-sql

Replace the 1 in the case with area like the msi77 said

 

From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@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-owner@postgresql.org [mailto:pgsql-sql-owner@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.

 

 

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Calculate the ratio
@ 2011-08-15 13:49  Andreas Forø Tollefsen <andreasft@gmail.com>
  parent: David Johnston <polobo@yahoo.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Andreas Forø Tollefsen @ 2011-08-15 13:49 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

Great. This works like I wanted.

Thanks!

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

end of thread, other threads:[~2011-08-15 13:49 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-08-15 12:21 Calculate the ratio Andreas Forø Tollefsen <andreasft@gmail.com>
2011-08-15 13:00 ` msi77 <msi77@yandex.ru>
2011-08-15 13:08 ` David Johnston <polobo@yahoo.com>
2011-08-15 13:10   ` David Johnston <polobo@yahoo.com>
2011-08-15 13:49     ` Andreas Forø Tollefsen <andreasft@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox