--- In firebird-support@yahoogroups.com, "Luciano Enzweiler"
<enzweiler@s...> wrote:

> One example of this is a list sum of the sold quantity for each item

between two dates. All the problem is if I don't use the view I cannot
sum the quantity of one item sold on two or more different dates, it
lists the same item more than once. So I use the view to make a
pre-sum and then aggregate it on another query:

>
> First I execute the drop:
> DROP VIEW ITEMSVIEW;
>
> Then I recreate it with the new date interval:
> CREATE VIEW ITEMSVIEW (QTY, TOTAL, CODE, NAME, PRICE, BRAND) AS

SELECT SUM(SI.TOTQTY), SUM(SI.TOTVL), I.CODE, I.DESCRIP, I.PRICE,
B.BRANDNAME

> FROM ((SALES S LEFT JOIN SALESITEMS SI ON S.CDSALES = SI.CDSALES)

LEFT JOIN ITEMS I ON I.CODE = S.ITEMCODE) LEFT JOIN BRAND B ON
B.CDBRAND = I.CDBRAND

> GROUP BY I.CODE, I.DESCRIP, I.PRICE, B.BRANDNAME, S.SALEDATE HAVING

S.SALEDATE BETWEEN '01/01/2004' AND '01/31/2004';

>
> Then I make the grouping select:
> SELECT SUM(QTY) AS TOTALQTY, SUM(TOTAL) AS TOTALVALUE, CODE, NAME,

PRICE, BRAND FROM ITEMSVIEW GROUP BY CODE, NAME, PRICE, BRAND

>
> Now each item has one overall sum of the quantity sold between the

dates. Hope someone has a better solution...

SELECT SUM(SI.TOTQTY), SUM(SI.TOTVL), I.CODE, I.DESCRIP, I.PRICE,
B.BRANDNAME
FROM ((SALES S LEFT JOIN SALESITEMS SI ON S.CDSALES = SI.CDSALES)
LEFT JOIN ITEMS I ON I.CODE = S.ITEMCODE) LEFT JOIN BRAND B ON
B.CDBRAND = I.CDBRAND
Where S.SALEDATE BETWEEN '01/01/2004' AND '01/31/2004'
GROUP BY I.CODE, I.DESCRIP, I.PRICE, B.BRANDNAM

?

IMO derived tables is needed only when you need aggregates on
aggregates, like

Select Avg(SumQ), Max(SumQ), Min(SumQ) From
(Select Sum(Quantity) SumQ
From ....)

Best regards,
Alexander.