🚀 go-pugleaf

Hs wrote:
>
>   1. SELECT SUM(BUY.WEIGHT), SUM(BUY.WEIGHT) * 100
>    FROM BUY WHERE BUY.PAID = 'N' GROUP BY BUY.FARMERID
>
>   2. SELECT FARMER.* FROM FARMER WHERE FARMER.ID IN
>    (SELECT BUY.FARMERID FROM BUY WHERE BUY.PAID = 'N' GROUP BY
> BUY.FARMERID)
>
>  - In first query I have to use SUM(BUY.WEIGHT) twice. will it
>   decrease performance? if yes how can I use it once?

If this is going to a report, the report engine could calculate the second 
sum * 100. It could also be done in the application instead of the query. 
However I doubt the performance different willr eally be noticeable. You can 
test it by looking at the stats for the query in IBConsole.

>  - Is there any way to combine thease two queries into one query?
> (their condition is same)

Assuming there is only going to be one farmer for each BUY record, then yes. 
The group by will need to include all fields from farmer that you select, so 
select the fewest possible:

    SELECT
        FARMER.ID, FARMER.NAME, FARMER.CITY, <etc>
        SUM(BUY.WEIGHT), SUM(BUY.WEIGHT) * 100
    FROM FARMER
    INNER JOIN BUY ON BUY.FARMERID = FARMER.ID
    WHERE BUY.PAID = 'N'
    GROUP BY BUY.FARMERID, FARMER.NAME, FARMER.CITY, <etc>

You should always use joins when possible instead of subqueries. Joins will 
be much faster in most cases.

-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Those who disdain wealth as a worthy goal for an individual or a
society seem not to realize that wealth is the only thing that can
prevent poverty." - Thomas Sowell