4 Replies Latest reply: Feb 22, 2013 11:29 PM by Phaneendra Kunche RSS

    SQL group by and aggregation functions not working

      Hi,

       

      I have a SQL which I am hitting from Qlikview. The SQL is as follows:

       

      SELECT  

        DM_STORE.LOCATION_NUMBER,

        DM_HOUR.HOUR_DESCRIPTION,

        DM_HOUR.HOUR_NUMBER,

        sum(DM_SALE_HOUR.US_AMOUNT),

        sum(DM_SALE_HOUR.UNIT_QTY)

      FROM

        DM_SALE_HOUR,

        DM_HOUR,

        DM_DAY,

        DM_STORE,

        DM_PRODUCT

      WHERE

        ( DM_SALE_HOUR.HOUR_ID=DM_HOUR.HOUR_ID  )

        AND  ( DM_DAY.DAY_ID=DM_SALE_HOUR.DAY_ID  )

        AND  ( DM_PRODUCT.PRODUCT_ID=DM_SALE_HOUR.PRODUCT_ID  )

        AND  ( DM_SALE_HOUR.STORE_ID=DM_STORE.STORE_ID  )

        AND  (

        (DM_PRODUCT.PSA_CATEGORY_CODE  IN  ('10.02', '10.03')

        OR   DM_PRODUCT.PSA_SUBCATEGORY_CODE  IN  ('11.01.02'))

        AND   DM_DAY.MONTH_ID  = 241

        AND  DM_HOUR.HOUR_DESCRIPTION  IN  ('2 PM to 3 PM', '3 PM to 4 PM', '4 PM to 5 PM', '5 PM to 6 PM', '6 PM to 7 PM', '7 PM to 8 PM')

        )

      GROUP BY

        DM_STORE.LOCATION_NUMBER,

        DM_HOUR.HOUR_DESCRIPTION,

        DM_HOUR.HOUR_NUMBER

       

      This doesn't work and the script stalls ( It doesn't even error out, it just stalls. The script time elapsed stalls at 1 second). But, if I remove the Group by Clause and "sum" in select statements, Qlikview pulls data. I did hit the above query in the database & it just works fine. I am not understanding what I am missing here. Please help.

       

      Thanks,

      Tutan