agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedcrosstab category mix
4+ messages / 3 participants
[nested] [flat]
* crosstab category mix
@ 2017-06-12 07:53 Majid Khan <mk.swati@gmail.com>
2017-06-12 13:46 ` Re: crosstab category mix Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 4+ messages in thread
From: Majid Khan @ 2017-06-12 07:53 UTC (permalink / raw)
To: pgsql-sql
Hi,
favorite
<https://stackoverflow.com/questions/44487568/postgres-cross-tabs-mixes-categories-in-result-set#;
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 ..
Date, District1, District2, District3, District4
While in my rows will be date along with the total value per district.
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.
I don't know why crosstab query mixes categories. Please help.
Regards,
Majid
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: crosstab category mix
2017-06-12 07:53 crosstab category mix Majid Khan <mk.swati@gmail.com>
@ 2017-06-12 13:46 ` Steve Midgley <science@misuse.org>
2017-06-16 12:35 ` Fwd: Re: crosstab category mix Majid Khan <mk.swati@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Steve Midgley @ 2017-06-12 13:46 UTC (permalink / raw)
To: Majid Khan <mk.swati@gmail.com>; +Cc: pgsql-sql
On Jun 12, 2017 12:53 AM, "Majid Khan" <mk.swati@gmail.com> wrote:
Hi,
favorite
<https://stackoverflow.com/questions/44487568/postgres-cross-tabs-mixes-categories-in-result-set#;
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 ..
Date, District1, District2, District3, District4
While in my rows will be date along with the total value per district.
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.
I don't know why crosstab query mixes categories. Please help.
Regards,
Majid
Please provide sample data and sql to allow us to see where your problem
is.
Steve
^ permalink raw reply [nested|flat] 4+ messages in thread
* Fwd: Re: crosstab category mix
2017-06-12 07:53 crosstab category mix Majid Khan <mk.swati@gmail.com>
2017-06-12 13:46 ` Re: crosstab category mix Steve Midgley <science@misuse.org>
@ 2017-06-16 12:35 ` Majid Khan <mk.swati@gmail.com>
2017-06-16 15:03 ` Re: crosstab category mix Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 4+ messages in thread
From: Majid Khan @ 2017-06-16 12:35 UTC (permalink / raw)
To: pgsql-sql
---------- Forwarded message ----------
From: "Majid Khan" <mk.swati@gmail.com>
Date: Jun 12, 2017 20:48
Subject: Re: [SQL] crosstab category mix
To: "Steve Midgley" <science@misuse.org>
Cc:
hi,
thanks for response. Here attached is the data with actual summary and
query result.Also I am applying the following query.
*SELECT * FROM crosstab('SELECT*
*survey_date,*
*district,*
*total*
*FROM*
*sample') AS ct(survey_date varchar, khuzdar NUMERIC ,loralai NUMERIC,zhob
NUMERIC);*
Regards,
Majid
On Mon, Jun 12, 2017 at 6:46 PM, Steve Midgley <science@misuse.org> wrote:
>
>
> On Jun 12, 2017 12:53 AM, "Majid Khan" <mk.swati@gmail.com> wrote:
>
> Hi,
> favorite
> <https://stackoverflow.com/questions/44487568/postgres-cross-tabs-mixes-categories-in-result-set#;
>
> 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 ..
>
> Date, District1, District2, District3, District4
>
> While in my rows will be date along with the total value per district.
>
> 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.
>
> I don't know why crosstab query mixes categories. Please help.
> Regards,
> Majid
>
>
> Please provide sample data and sql to allow us to see where your problem
> is.
>
> Steve
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Attachments:
[application/vnd.openxmlformats-officedocument.spreadsheetml.sheet] data.xlsx (13.3K, ../../CADbhJ9rYoSPJTNPXdmqtFp-nnD78La2fA+aJMw6_q1diuoZhOw@mail.gmail.com/3-data.xlsx)
download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: crosstab category mix
2017-06-12 07:53 crosstab category mix Majid Khan <mk.swati@gmail.com>
2017-06-12 13:46 ` Re: crosstab category mix Steve Midgley <science@misuse.org>
2017-06-16 12:35 ` Fwd: Re: crosstab category mix Majid Khan <mk.swati@gmail.com>
@ 2017-06-16 15:03 ` Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 4+ messages in thread
From: Samed YILDIRIM @ 2017-06-16 15:03 UTC (permalink / raw)
To: Majid Khan <mk.swati@gmail.com>; pgsql-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<...;
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-06-16 15:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-12 07:53 crosstab category mix Majid Khan <mk.swati@gmail.com>
2017-06-12 13:46 ` Steve Midgley <science@misuse.org>
2017-06-16 12:35 ` Majid Khan <mk.swati@gmail.com>
2017-06-16 15:03 ` Samed YILDIRIM <samed@reddoc.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox