pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed query
6+ messages / 5 participants
[nested] [flat]
* Need query
@ 2021-07-17 07:24 Firthouse banu <penguinsfairy@gmail.com>
2021-07-17 07:53 ` Re: Need query David G. Johnston <david.g.johnston@gmail.com>
2021-07-17 08:20 ` Re: Need query Imre Samu <pella.samu@gmail.com>
2021-07-17 08:32 ` Re: Need query Thomas Kellerer <shammat@gmx.net>
0 siblings, 3 replies; 6+ messages in thread
From: Firthouse banu @ 2021-07-17 07:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
TIA
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need query
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
@ 2021-07-17 07:53 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2021-07-17 07:53 UTC (permalink / raw)
To: Firthouse banu <penguinsfairy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Saturday, July 17, 2021, Firthouse banu <penguinsfairy@gmail.com> wrote:
>
>
> Can anyone write query to get this desired output from table one in
> Postgres please.
>
>
No, but I’ll point you in a useful direction.
https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need query
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
@ 2021-07-17 08:20 ` Imre Samu <pella.samu@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Imre Samu @ 2021-07-17 08:20 UTC (permalink / raw)
To: Firthouse banu <penguinsfairy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> Can anyone write query to get this desired output from table one in
Postgres please.
some links ; maybe useful :
https://stackoverflow.com/questions/3002499/postgresql-crosstab-query
https://dba.stackexchange.com/questions/38758/postgres-use-result-of-group-by-as-column-names
https://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by
keywords: crosstab, tablefunc,
Best,
Imre
Firthouse banu <penguinsfairy@gmail.com> ezt írta (időpont: 2021. júl. 17.,
Szo, 9:25):
> 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.
>
> TIA
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need query
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
@ 2021-07-17 08:32 ` Thomas Kellerer <shammat@gmx.net>
2021-07-18 13:19 ` Re: Need query Caetano Traina Junior <caetano@icmc.usp.br>
2 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2021-07-17 08:32 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need query
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
2021-07-17 08:32 ` Re: Need query Thomas Kellerer <shammat@gmx.net>
@ 2021-07-18 13:19 ` Caetano Traina Junior <caetano@icmc.usp.br>
2021-07-18 14:54 ` Re: Need query Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Caetano Traina Junior @ 2021-07-18 13:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Nonetheless, it is always possible to write a dynamic SQL (and best yet,
inside a plpgsql function) to "automagically handle more fruits..."
Em sáb., 17 de jul. de 2021 às 05:33, Thomas Kellerer <shammat@gmx.net>
escreveu:
> 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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Need query
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
2021-07-17 08:32 ` Re: Need query Thomas Kellerer <shammat@gmx.net>
2021-07-18 13:19 ` Re: Need query Caetano Traina Junior <caetano@icmc.usp.br>
@ 2021-07-18 14:54 ` Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2021-07-18 14:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Caetano Traina Junior schrieb am 18.07.2021 um 15:19:>
>> Em sáb., 17 de jul. de 2021 às 05:33, Thomas Kellerer <shammat@gmx.net <mailto:shammat@gmx.net>> escreveu:
>> 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.
> Nonetheless, it is always possible to write a dynamic SQL (and best
> yet, inside a plpgsql function) to "automagically handle more
> fruits..."
Even if you create a function that uses dynamic SQL, you still have to
specify each output column "manually" when calling the function.
So that doesn't really buy you anything.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2021-07-18 14:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-17 07:24 Need query Firthouse banu <penguinsfairy@gmail.com>
2021-07-17 07:53 ` David G. Johnston <david.g.johnston@gmail.com>
2021-07-17 08:20 ` Imre Samu <pella.samu@gmail.com>
2021-07-17 08:32 ` Thomas Kellerer <shammat@gmx.net>
2021-07-18 13:19 ` Caetano Traina Junior <caetano@icmc.usp.br>
2021-07-18 14:54 ` Thomas Kellerer <shammat@gmx.net>
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