CREATE TABLE PRODUCTS (
    ID           DINT_NOT_NULL NOT NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    ID_FAC       DINT_NULL DEFAULT 0 /* DINT_NULL = INTEGER */,
    ID_CAT       DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    PRODUCTNAME  DVCHAR_AVERAGE /* DVCHAR_AVERAGE = VARCHAR(150) */,
    UNITPRICE    DMONEY /* DMONEY = DECIMAL(15,2) */,
    DESCPROD     BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    UNITDIMEN    VARCHAR(5),
    QUANTSG      DINT_NULL /* DINT_NULL = INTEGER */,
    AKCIYA       SMALLINT DEFAULT 2
);


CREATE TABLE ORDERSDETAILS (
    ID                 DINT_NOT_NULL NOT NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    ORDID              DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    PRODUCTID          DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    QUANTITY           SMALLINT,
    UNITPRICE          DMONEY /* DMONEY = DECIMAL(15,2) */,
    DISCOUNT           DINT_NULL /* DINT_NULL = INTEGER */,
    TAKCIYA            SMALLINT DEFAULT 0,
    MOREINFOITEM       DVARCHAR /* DVARCHAR = VARCHAR(250) */,
    CURRMINFOMOREITEM  DMONEY DEFAULT 0 /* DMONEY = DECIMAL(15,2) */,
    TPRODUCT           SMALLINT DEFAULT 1,
    RESTITEM           SMALLINT DEFAULT 0
);

CREATE TABLE ORDERS (
    ID            DINT_NOT_NULL NOT NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    ORDERNUM      VARCHAR(50),
    ORDCOUNT      SMALLINT DEFAULT 0,
    CUSTOMERID    DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    OPERATORID    DINT_NOT_NULL /* DINT_NOT_NULL = INTEGER NOT NULL */,
    ORDERDATE     DDATE /* DDATE = DATE */,
    ORDERTIME     DTIME /* DTIME = TIME */,
    DELIVERYADR   VARCHAR(250),
    ITEMKCH       DINT_NULL /* DINT_NULL = INTEGER */,
    PKGONE        SMALLINT,
    ORDERSTATUS   SMALLINT DEFAULT 1,
    CURRDELAVERY  DMONEY DEFAULT 0 /* DMONEY = DECIMAL(15,2) */,
    ODISCONT      DMONEY DEFAULT 0 /* DMONEY = DECIMAL(15,2) */,
    DESCORDER     DVARCHAR /* DVARCHAR = VARCHAR(1024) */,
    MOREINFORDER  DVARCHAR /* DVARCHAR = VARCHAR(1024) */,
    APRINT        SMALLINT DEFAULT 0,
    APRKITCH      SMALLINT DEFAULT 0
);

CREATE INDEX ORDERS_IDX1 ON ORDERS (CUSTOMERID);
CREATE INDEX ORDERS_IDX2 ON ORDERS (ORDERSTATUS);
CREATE INDEX ORDERS_IDX3 ON ORDERS (ORDERNUM);
CREATE INDEX ORDERS_IDX4 ON ORDERS (ORDERDATE);
CREATE INDEX ORDERS_IDX5 ON ORDERS (ORDERTIME);

with tm as (
                  select od.productid,
                            sum(od.quantity) as qty , sum(od.quantity*od.unitprice) as proiz
                             from ordersdetails od inner join orders o on o.id = od.ordid
                 -- where o.orderdate between '01.02.2017' and '31.12.2017'
                 --       and o.itemkch = 0
                  group by od.productid

) select
     t.productid,
     p.productname,
     t.qty as qty,
     t.proiz as sm
  from tm t left join products p on t.productid = p.id