Query Example: Quantity, revenue, and cost by item

This example shows, for a given category, all of the items including quantity sold, revenue, and cost.

SELECT ITEM_DESC,
    SUM(QUANTITY_SOLD),
    SUM(EXTENDED_PRICE),
    SUM(EXTENDED_COST)
FROM PERIOD, DAILY_SALES,PRODUCT, STORE, PROMOTION
    WHERE
    PERIOD.PERKEY=DAILY_SALES.PERKEY AND
    PRODUCT.PRODKEY=DAILY_SALES.PRODKEY AND
    STORE.STOREKEY=DAILY_SALES.STOREKEY AND
    PROMOTION.PROMOKEY=DAILY_SALES.PROMOKEY AND
    CALENDAR_DATE BETWEEN '2011/01/01' AND '2011/01/31' AND
    STORE_NUMBER=01 AND
    PROMODESC IN ('Advertisement', 'Coupon', 'Weekly Special', 
       'Overstocked Items') AND
    CATEGORY=42
GROUP BY ITEM_DESC;