Hi,

Yes, Arno is correct. But the select statment given by
him gave wrong value.

When I tried the following statment it gave the correct
value, but the table access is more than once.
Any other workaround ?

SELECT PR.product_nm,
( SELECT SUM( SD.quantity ) FROM sale_det SD
WHERE ( PR.id = SD.product_id ) ) AS s_qty,
( SELECT SUM( SD.product_value ) FROM sale_det SD
WHERE ( PR.id = SD.product_id ) ) AS s_value,
( SELECT SUM( PD.quantity ) FROM purchase_det PD
WHERE ( PR.id = PD.product_id ) ) AS p_qty,
( SELECT SUM( PD.w_qty * PD.rate ) FROM purchase_det PD
WHERE ( PR.id = PD.product_id ) ) AS p_value
FROM product PR

Thank you,

Sudheer Palaparambil

On Thu, 18 Nov 2004 09:34:36 +0100, Arno Brinkman <firebird@...> wrote:
> Hi,
>
> > The simple answer is "Learn basic SQL", e.g. at www.w3schools.com/sql/
>
> Certainly ;)
>
>
>
> > The answer you're looking for is
> >
> > select pd.product_id,
> > sum(sd.qty_sold) as sold_qty,
> > sum(pd.qty_purchased) as purchased_qty,
> > sum(pd.value) as purchase_value
> > from sale_det sd
> > join purchase_det pd on pd.product_id = sd.product_id
> > group by pd.product_id
> >
> > I assume that you want to sum the product_det as well, even though
> > your example doesn't require it.
>
> If that is the case then your above solution will be not given the expected
> result, because on duplicate "purchase_det.product_id" entries the sum for
> "sale_det.qty_sold" will include the same "sale_det" record x-times.
>
> I guess this is what he wants, but if he wants to see all products (even
> those that doesn't have sales) then he should join with products and left
> join the other tables.
>
> SELECT
> sd.product_id,
> sum(sd.qty_sold) as sold_qty,
> pd.qty_purchased as purchased_qty,
> pd.value as purchase_value
> FROM
> sale_det sd
> JOIN purchase_det pd ON (pd.product_id = sd.product_id)
> GROUP BY
> sd.product_id,
> pd.qty_purchased,
> pd.value
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Firebird open source database (based on IB-OE) with many SQL-99 features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
> Support list for Interbase and Firebird users :
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep :
> news://newsgroups.firebirdsql.info
>
>
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>
> ________________________________
> Yahoo! Groups Links
>
> To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.