Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 62FAEB5DBD5 for ; Mon, 15 Aug 2011 10:11:20 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 63533-08 for ; Mon, 15 Aug 2011 13:11:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from smtp108.prem.mail.ac4.yahoo.com (smtp108.prem.mail.ac4.yahoo.com [76.13.13.47]) by mail.postgresql.org (Postfix) with SMTP id 5B4A5B5DBD8 for ; Mon, 15 Aug 2011 10:11:12 -0300 (ADT) Received: (qmail 31169 invoked from network); 15 Aug 2011 13:11:12 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=hnCucrguTtBPG1T7Nuk+ZuQHlYDrbfFCTrrn7cUTGKMr82DTi9miyoSQlofbenbKaqK4DJeiY+GLXd7zb68aAlsG3ZHOsUQDn0ROBgt5bsTjzqMXz8MsJ5n609se9aSbgJyfw6TVGktgu3hzGD86pd6MM4Km+tlYp1dAT8zLAAI= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1313413872; bh=7VRkcVzHi/TmEVHapved/FnbFLq5oh0zlmlsx46ocrQ=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:From:To:References:In-Reply-To:Subject:Date:Message-ID:MIME-Version:Content-Type:X-Mailer:Thread-Index:Content-Language; b=Tm4M+YDMPTlv53OOfPy64oNFf+je55GDRN0HNzDW28S14akC9U0DwpqexKlB71xWbWxUtEdWuu88CP85DwErtRA6dQSX5B3hKH32lIQIRmip5hbyqaAWujwl13CXYfuf3Z/BZAxxw21vUiWVTuryIY+U+XoxFEvSeAqkQHfJs/8= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: FplYjCAVM1m4LFKjK7p91IEBTscyOagoesjt1.nwA5_rUJ5 zVyi56_9g18jgguAD2aV58aNSBjmFszPgqIVRJle6GjpuQg0UUiLriQTm0ef DH_c6gsz07uZhFfyEOE.pjol4TFLUWH.0uqK3ZR2oCgGjBv3hdOky2Hk_3aD tIwmmPB9jDyy2g7qPOuGEgERWlmP4YPgjmXDTZ0.VpTCzx0dJGmYNPeOMJqz LF1oGsSlzOQe0QyhA.XrrqKGKQBj3hVa6tZY6RzIHonzmctRCSGLO5.gCNC6 n1SgQK.EDH9hOUgJIJ7uyit3ACc.4oDwyBMTwD0fE1RS6QkVaERkT4tp4NdT gQMKb3i5zNTv3_UqL3Ry8rZHxcijJAYJa4.JU1KzBmU9T_KECS1cCQpolOOW c25BOcA-- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@66.67.24.143 with login) by smtp108.prem.mail.ac4.yahoo.com with SMTP; 15 Aug 2011 06:11:12 -0700 PDT From: "David Johnston" To: =?iso-8859-1?Q?'Andreas_For=F8_Tollefsen'?= , References: <003901cc5b4c$688042a0$3980c7e0$@yahoo.com> In-Reply-To: <003901cc5b4c$688042a0$3980c7e0$@yahoo.com> Subject: Re: Calculate the ratio Date: Mon, 15 Aug 2011 09:10:56 -0400 Message-ID: <004401cc5b4c$c5513f20$4ff3bd60$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0045_01CC5B2B.3E41E910" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQMYbMrr/0WypYOeaR+ub0P8+iTEswK6XS0Akm/sxFA= Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.968 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-1.071, UNPARSEABLE_RELAY=0.001 X-Spam-Level: X-Archive-Number: 201108/47 X-Sequence-Number: 35715 This is a multipart message in MIME format. ------=_NextPart_000_0045_01CC5B2B.3E41E910 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Replace the =931=94 in the case with =93area=94=85 like the msi77 said =20 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=F8 Tollefsen'; pgsql-sql@postgresql.org Subject: Re: [SQL] Calculate the ratio =20 SELECT cols, SUM( CASE WHEN cf =3D 1 THEN 1 ELSE 0 END ) AS sum_1, SUM ( CASE WHEN cf =3D 0 THEN 1 ELSE 0 END ) AS sum_0 FROM table WHERE cf IN (0, 1) GROUP BY cols =20 =20 From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas For=F8 Tollefsen Sent: Monday, August 15, 2011 8:22 AM To: pgsql-sql@postgresql.org Subject: [SQL] Calculate the ratio =20 Hi all, =20 This is probably quite simple. I want to calculate the ratio of each = country (gwcode) which experiences a 1 in one column (cf). =20 Structure: =20 gid; gridyear; gwcode; area; cf =20 I want to select the SUM(area) WHERE cf =3D 1 into one column, and = SUM(area) WHERE cf =3D 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. =20 Any suggestions? =20 Thanks. =20 A. =20 =20 ------=_NextPart_000_0045_01CC5B2B.3E41E910 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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=F8 Tollefsen'; = pgsql-sql@postgresql.org
Subject: Re: [SQL] Calculate the = ratio

 

SELECT cols,

SUM( CASE WHEN cf =3D 1 THEN 1 ELSE 0 END ) AS = sum_1,

SUM ( CASE WHEN cf =3D 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=F8 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 =3D 1 into one column, and SUM(area) WHERE cf =3D 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.

 

 

------=_NextPart_000_0045_01CC5B2B.3E41E910--