Re: BUG #10405: Sum not working with left join

El 20/05/2014 05:39 a.m., cousinka(at)gmail(dot)com escribió:
> The following bug has been logged on the website:
>
> Bug reference: 10405
> Logged by: Zoltan Jozsa
> Email address: cousinka(at)gmail(dot)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