SELECT c.CLIENT_CODE , c.CLIENT_NAME , c.CLIENT_TYPE , d.CLIENT_MEMBER_CODE AS DEALER_CODE , c.CLIENT_ID , d.ID AS DEALER_ID , c.PRODUCT_TYPE_ID , c.SECURITY_ID , c.SYMBOL , c.SERIES , c.BUY_VALUE , c.BUY_QUANTITY , c.BUY_AVG_PRICE , c.SELL_VALUE , c.SELL_QUANTITY , c.SELL_AVG_PRICE FROM (SELECT a.CLIENT_CODE, a.CLIENT_NAME, a.CLIENT_ID, a.CLIENT_TYPE, a.BUY_VALUE, a.BUY_QUANTITY, a.BUY_AVG_PRICE, a.SECURITY_ID, a.SELL_VALUE, a.SELL_AVG_PRICE , a.SELL_QUANTITY , a.PRODUCT_TYPE_ID , a.SYMBOL , a.SERIES , b.DEALER_ID FROM
  (SELECT y.client_member_code      AS CLIENT_CODE,
          y.display_name            AS CLIENT_NAME,
          z.client_dealer_type_name AS CLIENT_TYPE,
     x.client_id,
     x.product_type_id,
     x.security_id,
     x.symbol,
     x.series,
          SUM(x.buy_value)          AS BUY_VALUE,
          SUM(x.buy_quantity)       AS BUY_QUANTITY,
          SUM(x.buy_avg_price)      AS BUY_AVG_PRICE,
          SUM(x.sell_value)         AS SELL_VALUE,
          SUM(x.sell_quantity)      AS SELL_QUANTITY,
          SUM(x.sell_avg_price)     AS SELL_AVG_PRICE
   FROM  (SELECT h.client_id,
            h.product_type_id,
            h.buy_or_sell,
            h.SECURITY_ID,
            S2.symbol,
            S3.series,
            CASE
            WHEN ( h.buy_or_sell = 1 ) THEN SUM(
                e.trade_price * e.quantity_traded)
            END AS BUY_VALUE,
            CASE
            WHEN ( h.buy_or_sell = 1 ) THEN SUM(e.quantity_traded)
            END AS BUY_QUANTITY,
            CASE
            WHEN ( h.buy_or_sell = 1 ) THEN AVG(trade_price)
            END AS BUY_AVG_PRICE,
            CASE
            WHEN ( h.buy_or_sell = 2 ) THEN SUM(
                e.trade_price * e.quantity_traded)
            END AS SELL_VALUE,
            CASE
            WHEN ( h.buy_or_sell = 2 ) THEN SUM(e.quantity_traded)
            END AS SELL_QUANTITY,
            CASE
            WHEN ( h.buy_or_sell = 1 ) THEN AVG(trade_price)
            END AS SELL_AVG_PRICE
          FROM   trade_book e
            JOIN ORDER_BOOK_HEADER h
              ON e.EXCHANGE_ORDER_ID = h.EXCHANGE_ORDER_ID
            JOIN securities S2
              ON h.security_id = S2.id
            JOIN series_master S3
              ON S2.series = S3.id
          WHERE  h.product_type_id=1
          GROUP  BY h.product_type_id,
            h.client_id,
            h.SECURITY_ID,
            S2.symbol,
            S3.series,
            h.buy_or_sell) AS x
     JOIN client_dealer_master y
       ON x.client_id = y.id
     JOIN client_dealer_types z
       ON y.client_dealer_type_id = z.id
   GROUP  BY y.client_member_code,
     z.client_dealer_type_name,
     y.display_name,
     x.client_id,
     x.product_type_id,
     x.symbol,
     x.series,
     x.security_id) AS a join CLIENT_DEALER_MAPPING b ON a.CLIENT_ID = b.CLIENT_ID) AS c join client_dealer_master d ON
                                                                                                                       c.DEALER_ID=d.id WHERE c.CLIENT_ID=:clientId AND c.DEALER_ID=:dealerId AND c.PRODUCT_TYPE_ID=:productId