BUG #10405: Sum not working with left join

On 5/20/2014 1:39 AM, cousinka@gmail.com wrote:

I am a programmer from transylvania, i have a task where i have to get a
list of offer requests ordered by rate, but it's not working for me.

the bug report list is NOT a suitable forum for this 'how to' discussion.

--
john r pierce 37N 122W
somewhere on the middle of the left coast

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

El 20/05/2014 05:39 a.m., cousinka@gmail.com escribió:

The following bug has been logged on the website:

Bug reference: 10405
Logged by: Zoltan Jozsa
Email address: cousinka@gmail.com
PostgreSQL version: 9.1.12
Operating system: Linux
Description:

Hello.
I am a programmer from transylvania, i have a task where i have to get a
list of offer requests ordered by rate, but it's not working for me.

I have written an SQL:
SELECT
OFREQD.offer_request_id,
OFREQD.offer_text,
OFREQD.offer_date,CD.company_name, ( sum( orr.rate ) ) AS offer_rating

FROM offer_request_data AS OFREQD
JOIN offer_message_data AS OFMD ON ( OFREQD.offer_request_id =
OFMD.offer_request_id )
JOIN company_data AS CD ON ( OFMD.recipient_company_id = CD.company_id )
LEFT JOIN offer_request_rating orr ON orr.offer_request_id =
OFREQD.offer_request_id

WHERE ( OFREQD.activity_id = 3239 OR OFMD.activity_id=3239 )
AND OFREQD.is_checked = 1
AND OFREQD.is_spam=0

GROUP BY OFREQD.offer_request_id,OFREQD.offer_text,CD.company_name
ORDER BY offer_rating DESC

OFFSET 0 LIMIT 10

Please take a look on this SQL, and let me know if something is wrong in
SQL, or what can I do to do what the Client ask.

If in the SELECT is SUM, then the server returns NULL as offer_rating for
every row,
if is COUNT then works fine.

Thank you,
Cousin!

hi, all
perhaps

sum( coalesce(orr.rate,0) ), solves your problem

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs