From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Need query
Date: Sat, 17 Jul 2021 10:32:45 +0200
Message-ID: <d3309e65-ddff-439e-49a1-7a42a0479001@gmx.net> (raw)
In-Reply-To: <CAK_Ha4xccRpDqzC-M5NWXn1+saRXbC_PVn3Rw0Lo=qBHxQDhuw@mail.gmail.com>
References: <CAK_Ha4xccRpDqzC-M5NWXn1+saRXbC_PVn3Rw0Lo=qBHxQDhuw@mail.gmail.com>
Firthouse banu schrieb am 17.07.2021 um 09:24:
> Table1
> Name Fruit
> A Mango
> A Apple
> A Grape
> B. Mango
> B. Mango
> A. Mango
> A. Apple
>
> Output
> Name. Mango Apple Grape
> A. 2. 2. 1
> B. 2. 0. 0
>
> Can anyone write query to get this desired output from table one in Postgres please.
In my opinion, filtered aggregation is the most flexible way to do this kind of thing
select name,
count(*) filter (where fruit = 'Mango') as mango,
count(*) filter (where fruit = 'Apple') as apple,
count(*) filter (where fruit = 'Grape') as grape
from the_table
group by name
order by name;
And before you ask: it's not really possible to make this dynamic, so that the number
of columns "automagically" increases when you have more fruits. One fundamental
restriction of the SQL language is that the number and types of all columns
must be known to the database when the query is parsed. It can't change
the columns as a result of the query itself.
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-admin@postgresql.org
Cc: shammat@gmx.net, pgsql-admin@lists.postgresql.org
Subject: Re: Need query
In-Reply-To: <d3309e65-ddff-439e-49a1-7a42a0479001@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox