agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Samed YILDIRIM <samed@reddoc.net>
To: Majid Khan <mk.swati@gmail.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: crosstab category mix
Date: Fri, 16 Jun 2017 18:03:43 +0300
Message-ID: <1967471497625423@web18j.yandex.ru> (raw)
In-Reply-To: <CADbhJ9rYoSPJTNPXdmqtFp-nnD78La2fA+aJMw6_q1diuoZhOw@mail.gmail.com>
References: <CADbhJ9pVMtWixJGQQsY-N=gdHND65df56QYe8uUFpbWK5L7mpQ@mail.gmail.com>
<CAJexoSKHupKeUK1UFwOkgHUi_NYEmRax_6+L6EbRj_jmvr2p5Q@mail.gmail.com>
<CADbhJ9o9gmpp2uBqjyXPZCTo8EwvGNPkY6vu1aYogtgsvF7D1g@mail.gmail.com>
<CADbhJ9rYoSPJTNPXdmqtFp-nnD78La2fA+aJMw6_q1diuoZhOw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
<div>Hi Majid,</div><div> </div><div>Use following query to get desired output.</div><div>select * from crosstab('select * from khan order by 1,2'<strong>,'select distinct district from khan order by 1'</strong>) as ct(survey_date date, Khuzdar int, Loralai int, Zhob int);</div><div> </div><div>By using this query, you have null values on some columns. To sum these columns, you can use coalesce function as in the below example.</div><div>select *,coalesce(Khuzdar,0)+coalesce(Loralai,0)+coalesce(Zhob,0) as total from crosstab('select * from khan order by 1,2','select distinct district from khan order by 1') as ct(survey_date date, Khuzdar int, Loralai int, Zhob int);</div><div> </div><div>You can have a look crosstab(text,text) in documentation.</div><div>https://www.postgresql.org/docs/9.6/static/tablefunc.html</div><div> </div><div... çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>16.06.2017, 15:36, "Majid Khan" <mk.swati@gmail.com>:</div><blockquote type="cite"><div> <div>---------- Forwarded message ----------<br />From: "Majid Khan" <<a href="mailto:mk.swati@gmail.com">mk.swati@gmail.com</a>><br />Date: Jun 12, 2017 20:48<br />Subject: Re: [SQL] crosstab category mix<br />To: "Steve Midgley" <<a href="mailto:science@misuse.org">science@misuse.org</a>><br />Cc:<br /> <blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>hi,<div> </div><div>thanks for response. Here attached is the data with actual summary and query result.Also I am applying the following query.</div><div> </div><div><div><strong><font color="#0000ff">SELECT * FROM crosstab('SELECT</font></strong></div><div><strong><font color="#0000ff">survey_date,</font></strong></div><div><strong><font color="#0000ff">district,</font></strong></div><div><strong><font color="#0000ff">total</font></strong></div><div><strong><font color="#0000ff">FROM</font></strong></div><div><strong><font color="#0000ff">sample') AS ct(survey_date varchar, khuzdar NUMERIC ,loralai NUMERIC,zhob NUMERIC);</font></strong></div><div> </div></div><div><font color="#000000">Regards,</font></div><div><font color="#000000">Majid</font></div></div><div><div> <div>On Mon, Jun 12, 2017 at 6:46 PM, Steve Midgley <span><<a target="_blank" href="mailto:science@misuse.org">science@misuse.org</a>></span> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div><div><div> <div> <div>On Jun 12, 2017 12:53 AM, "Majid Khan" <<a target="_blank" href="mailto:mk.swati@gmail.com">mk.swati@gmail.com</a>> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><div><div style="margin:0in 0in 0pt;"><div style="font-size:18pt;"><span style="font-size:12pt;line-height:115%;"><font size="2" face="arial, helvetica, sans-serif" color="#000000">Hi,</font></span></div><div><table style="font-size:13px;margin:0px;padding:0px;border:0px;font-variant-numeric:inherit;font-stretch:inherit;line-height:inherit;font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;vertical-align:baseline;border-collapse:collapse;"><tbody style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><tr style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><td style="padding:0px 15px 0px 0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:top;"><div style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;text-align:center;min-width:46px;"><span style="line-height:115%;"><a title="Click to mark as favorite question (click again to undo)" target="_blank" style="margin:0px auto 2px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:1px;line-height:inherit;font-family:inherit;vertical-align:baseline;background-image:url('');background-size:initial;background-repeat:no-repeat;overflow:hidden;display:block;width:40px;height:30px;background-position:0px -120px;" href="https://stackoverflow.com/questions/44487568/postgres-cross-tabs-mixes-categories-in-result-set#"... style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"> </div></div></td><td style="padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:top;"><div style="margin:0px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;"><div style="margin:0px 0px 5px;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:15px;line-height:1.3;font-family:inherit;vertical-align:baseline;width:660px;"><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I have a query returning me three columns 'Date', 'District' and 'Total'. There are four districts but some time a district may not have record for corresponding date. I want to put all my districts in column and their total in rows as ..</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">Date, District1, District2, District3, District4</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">While in my rows will be date along with the total value per district.</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I am using crosstab to get the desired results. But in the result total for one district is listed in other district though the datewise grand total is same.</span></p><p style="margin:0px 0px 1em;padding:0px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;font-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inherit;vertical-align:baseline;clear:both;"><span style="line-height:115%;">I don't know why crosstab query mixes categories. Please help.</span></p></div></div></td></tr></tbody></table><span style="line-height:115%;"><font color="#000000"><font face="arial, helvetica, sans-serif">Regards,</font></font></span></div><div><span style="line-height:115%;"><font face="arial, helvetica, sans-serif" color="#000000">Majid</font></span></div></div></div></div></blockquote></div></div></div><div> </div></div></div><div><div><div><blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div> </div></blockquote></div><span style="font-family:sans-serif;">Please provide sample data and sql to allow us to see where your problem is. </span></div><div> </div><div><span><font color="#888888"><span style="font-family:sans-serif;">Steve </span></font></span></div></div></div></blockquote></div> <div> </div></div></div></blockquote></div></div>,<p> </p><span>--<br />Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br />To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql<...;
view thread (4+ messages)
Message-ID: <1967471497625423@web18j.yandex.ru>
Permalink: ../1967471497625423@web18j.yandex.ru/
Also on: postgresql.org/message-id/1967471497625423@web18j.yandex.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: samed@reddoc.net, mk.swati@gmail.com
Subject: Re: crosstab category mix
In-Reply-To: <1967471497625423@web18j.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox