Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id B4826B5DBD8 for ; Mon, 15 Aug 2011 10:08:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 63314-06 for ; Mon, 15 Aug 2011 13:08:37 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from smtp110.prem.mail.ac4.yahoo.com (smtp110.prem.mail.ac4.yahoo.com [76.13.13.93]) by mail.postgresql.org (Postfix) with SMTP id 27975B5DBD0 for ; Mon, 15 Aug 2011 10:08:37 -0300 (ADT) Received: (qmail 49273 invoked from network); 15 Aug 2011 13:08:36 -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=HoTijdqYE0fy8YFGe31LmtOfD177G7PNYIUfEve+XfFuYnncXrwKPMJvYgT1w1VHtPWCteaNIqVOSUtWre2NJYBnsddIaVFZKBVWBlMhTd++CbLuyyt0foewtLaN822HfxabZHX71rvBlOQ7qA22OMAa6w8E+LNzwz/f8pJahWw= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1313413716; bh=Wn60JPPAGNi8IR0DxlH5LB5QfC1Q2cHGGHO5ixRedUk=; 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=WxBsKfBeDJuY5dKLABKx2NNp0FvYaclO68fDp33yYk9ngE3HoWp8egOdXU70SyYfFxV+ToSjlbAHg0NrJyDJnwb+1zqBh0G/sTu9XXmG+4TOaNS09hT24+5JWLlLXEr9FXwTu/+yGzGOPH6KJwxfT7jiQ1X5yajpb7ztSOqIMbw= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: .uUGU3IVM1m.euvpDPt9lp0q4kA6WOHqO56iHSnUPxGVVAx 78PhTeLSAOKaVbt5gP0bESzMIC2hWnfjEPF4Cy8se1alIItqYaljydTIcSaE AoTQBUtly0t_FHNPVP3BfRpJOgPJbtJyjkcanAqjGg2nUd.5gI6vCufAczW9 O.8nmCbPUbr3_4auZhIBAqwQio4.DIBUATw2ykiOA8GcklVs2jnkqXADXBGg Ggbg1EVc1UCeapKBlB9HTo71Apq.T3STcNzCdNFdoNXDJG7AZphuHxmlESPU fahe3tdq1vwTNbbNYcFaWgJcbwmOGTpUAwBUMQKL7wy8Qc7j.yeh2smN5Sa9 wMaqp9Ptb.R8Lg_xWMSFYXUKJTzfBHX8duzwwltl3bauayMQiM91hLLFA_Yq kwq.kYQ-- X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from WolfDog (polobo@66.67.24.143 with login) by smtp110.prem.mail.ac4.yahoo.com with SMTP; 15 Aug 2011 06:08:36 -0700 PDT From: "David Johnston" To: =?iso-8859-1?Q?'Andreas_For=F8_Tollefsen'?= , References: In-Reply-To: Subject: Re: Calculate the ratio Date: Mon, 15 Aug 2011 09:08:20 -0400 Message-ID: <003901cc5b4c$688042a0$3980c7e0$@yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003A_01CC5B2A.E17113A0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQMYbMrr/0WypYOeaR+ub0P8+iTEs5KFvrvg 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/46 X-Sequence-Number: 35714 This is a multipart message in MIME format. ------=_NextPart_000_003A_01CC5B2A.E17113A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_003A_01CC5B2A.E17113A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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_003A_01CC5B2A.E17113A0--