Unable to filter data from the query

• Jeffrey Williams

  SSC Guru

  Points: 90250

  More actions

  November 25, 2021 at 3:39 pm

  #3956880

  As was noted before - using both DISTINCT and GROUP BY in the same query is meaningless.  A GROUP BY will return distinct rows based on the defined grouping - and DISTINCT will return distinct rows based on the column list.

  In general (there are always exceptions) - you should use GROUP BY when you want to aggregate data (SUM, AVG, COUNT, MIN, MAX) and DISTINCT when you don't need any aggregated data.  If you end up using DISTINCT because you have duplicate rows - then you need to review the query and determine why there are duplicates and if possible fix that issue.

  To filter data in a query - you can use a WHERE or if using GROUP BY you can use HAVING.  If - for example - you want to exclude rows where the SUM of the column is zero, you could use HAVING (and use a CASE expression):

  ,Dailycheck = SUM(   CASE
                                 WHEN a.TrnYear = '2022'
                                      AND (a.ProductClass NOT IN ( '_FRT', 'CXXX' )) THEN
                                     a.NetSalesValue
                                 ELSE
                                     0
                             END)
  ...
  HAVING
       SUM(CASE WHEN a.TrnYear = '2022'
                 AND a.ProductClass NOT IN ( '_FRT', 'CXXX' )) THEN a.NetSalesValue
                ELSE 0
                 END) = 0

   

   

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs