Dúvida Postgresql

Asked

Viewed 74 times

1

Good morning. I have the following SQL working:

Select g.galeria,g.tipo,g.finalidade,count(p.galeria) as total
from pessoas p
full join galerias g
on p.galeria = g.galeria
group by g.galeria,g.tipo,g.finalidade
order by g.galeria

However I need to show this result in a continuous line (string), I tried so:

Select string_agg(g.tipo::text || ' ' || g.galeria::text || ' (' || g.finalidade::text || '): ' || count(p.galeria) as total, ',') from galerias g
full join pessoas p
on p.galeria = g.galeria

The warning appears: ERROR: syntax error at or near "AS"

Any idea?

Grateful for the answers Marcos, Ricardo and Piovezan...but now another mistake arises:

Select string_agg(g.tipo::text || ' ' || g.galeria::text || ' (' || g.finalidade::text || '): ' || count(p.galeria) , ',') as total from galerias g
FULL JOIN pessoas p
ON p.galeria = g.galeria

ERROR: Aggregate Function calls cannot be nested

Grateful Anonimo...your suggestion worked with small changes:

WITH quantidade as(
    Select g.galeria, g.tipo, g.finalidade, count(p.galeria) as total
    from presos p
    full join galerias g
    on p.galeria = g.galeria
    group by g.galeria,g.tipo,g.finalidade
) 
Select string_agg(g.tipo::text || ' ' || g.galeria::text || ' (' || g.finalidade::text || '): ' || q.total, ',') 
from galerias g full join quantidade q on (q.galeria = g.galeria and q.tipo = g.tipo and q.finalidade = g.finalidade)
WHERE g.galeria <= 50
AND g.situacao = 'ATIVA'
group by g.galeria,g.tipo,g.finalidade
order by g.galeria

I thank you all for your cooperation!

sql postgresql delphi

  • count(p.galeria) as total should not close the parentheses of string_agg before the "the"?

  • Is that count(p.galeria) as total, little grasshopper, which I think should be just count(p.galeria) and if the intention is to rename the result to total (which is what the as does) has to stay out like this, count(p.galeria), ',') as total

  • About the error 'Aggregate Function calls [...]', you are using a Count() inside a string_agg()... This is causing the failure, to avoid, you should use some strategy. I suggest a subquery for the total.

  • 1

    @Jones713 Understand how the site works. It is a question at a time, solved this you accept the answer you solved (click on the little sign to the left of the answer) and if another problem occurs you open another question. The concept of the site is Q&A and not forum. This is how you mark "Solved" and not in the title.

2 answers

2

Hello...

In this passage

Count(p.gallery) the total, ',')'

Remove the 'as total'. It’ll stay that way:

... count(p.galeria), ',')

And you can give a nickname to the final result:

... count(p.galeria) , ',') as total

You should only use the alias via 'as' (in this context) when changing the name of the result column. Even, you can also omit 'as':

... count(p.galeria) , ',') total

2


I do not know if I understood the desired result but I believe that the use of Common Table Expression can help you.

WITH quantidade(
    Select g.galeria, g.tipo, g.finalidade, count(p.galeria) as total
    from pessoas p
    full join galerias g
    on p.galeria = g.galeria
    group by g.galeria,g.tipo,g.finalidade
) 
Select string_agg(g.tipo::text || ' ' || g.galeria::text || ' (' || g.finalidade::text || '): ' || q.total, ',') 
from galerias g full join quantidade q on (q.galeria = g.galeria and q.tipo = g.tipo and q.finalidade = g.finalidade);

Browser other questions tagged sql postgresql delphi

You are not signed in. Login or sign up in order to post.