On 2012-08-01 23:59, David Johnston wrote:
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of Condor
Sent: Wednesday, August 01, 2012 4:16 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Need help with SQL query and finding NULL
array_agg
Hello ppl,
I have a problem with one sql query, can some one help me. My query
is:
SELECT array_agg(month) AS month, array_agg(status) AS status,
array_agg(service) AS service, case when
array_upper(array_agg(phone),
1) is not null THEN array_agg(phone) else array_agg(mobile) END FROM
bills
WHERE status > 1 GROUP BY mobile
I try with simple query to identify did array_agg(phone) is empty or
null and if
is it to return me mobile field, if not empty to return me phone.
Mobile field
exist always, but phone may exists for that mobile may not exists.
One mobile can have few services like:
mob service phone
1321543434 64
1321543434 66 13255555
I try few thing but sql only return me records that phone is not
empty, but I
need them both.
Any one has ideas what I can do ?
ARRAY_AGG() is never an empty array since there is always at least a
single record that is going to be aggregated. In your case your
array
will have NULL "values" when phone numbers are missing but the upper
bound will still show a positive number.
SELECT array_upper(ARRAY[NULL]::varchar[], 1) == 1
You would need to write a custom aggregation that ignores NULL and
thus could return an empty array if no valid phone numbers are
present.
The proper logic would be:
CASE WHEN array_upper(phone_agg, 1) = 1 AND phone_agg[1] IS NULL THEN
... END
You also likely want to use:
ARRAY_AGG(DISTINCT column) -- this makes sure duplicates are only
present a single time and ensure that an all-NULL situation results
in a single element instead of one NULL for each input record.
Hopefully this help because I couldn't make heads nor tails as to
what exactly your issue is. The lack of input data, the current
output, and the desired output limits my ability to understand and
help.
One last comment: I would generally avoid naming the output of an
ARRAY_AGG(column) the same name as the input column. I generally, at
minimum, make the output column name plural to reflect the fact that
it contains multiple values of whatever is stored in the source
column.
David J.
Hello,
I understand what is the problem, but I can't combine your example with
my case.
I write my example in middle of the night and may be I miss to say
explain much more
about the structure:
mob service phone month
132999 64 1
1321543434 66 13255555 1
1321543434 67 13255555 2
First record when phone is empty and mob is 132999 the number is
correct.
Second two records also is correct, but the phone is not empty so I
need
that filed phone, they have services and month when to start.
I'm unable to use phone_agg[1] IS NULL because sql return me error
that can't
use the phone_agg[1]
Im expect that result:
month | status | service | array_agg
---------+---------+---------+---------------------------
{07} | {0} | {64} | {132999}
{08,07} | {0,0} | {66,67} | {13255555,13255555}
In the end I will have arrays for every phone which service will use.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
