-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of Condor
Sent: Thursday, August 02, 2012 4:35 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Need help with SQL query and finding NULL
array_agg
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.
Hopefully this will help.
The first thing I did was break up the query into parts
0) data
1) aggregation
2) conditional return
Note I am using the ability for the CTE to provide column names so
the contained queries are not cluttered with "AS alias" constructs.
In order to make things simpler I avoid storing NULL in the "phones"
array and instead store "N/A" if the phone is missing. This lets me
use " op ANY/ALL(array)" later on to check on the contents of the
array. The result of that condition is called "final_phones" and it
either matches the "mobs" or the "phones" array depending on whether
all of the "phone" numbers are missing.
Another option is to use the "masterphones" array where the value
stored into the array is the "phone" number if present otherwise it
is
the "mob" number.
WITH data (mob, service, phone, mth) AS (
VALUES
('132999','64',NULL,'1')
, ('132999','65','12345','1')
, ('1321543434','66','13255555','1')
, ('1321543434','67','13255555','2')
)
, maingroup (mob, mobiles, services, phones, months, masterphones) AS
(
SELECT mob, array_agg(mob), array_agg(service),
array_agg(COALESCE(phone,'N/A')), array_agg(mth),
array_agg(COALESCE(phone, mob))
FROM data
GROUP BY mob
)
SELECT *, CASE WHEN 'N/A' = ALL(phones) THEN mobiles ELSE phones END
AS final_phones
FROM maingroup
David J.
