Hi Majid,
Use following query to get desired output.
select * 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);
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.
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);
You can have a look crosstab(text,text) in documentation.
https://www.postgresql.org/docs/9.6/static/tablefunc.html
İyi çalışmalar.
Samed YILDIRIM
16.06.2017, 15:36, "Majid Khan" <mk.swati@gmail.com>:
---------- 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,
| 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