Count distinct one column based on criteria on another few columns

user_10813838 0
Viewed 125

I have sample data:: Data-Sample

  Country   Year  Month  CustID   Sales    Point   TestSUM  OrderType 
 Singapore  2018     10  AAA      623.96      520  1143.96          1 
 Singapore  2018     10  AAA       92.16        0    92.16          3 
 Singapore  2018     11  AAA     1168.28  1002.86  2171.14          1 
 Singapore  2018     11  BBB      118.58   103.25   221.83          1 
 Singapore  2018     10  CCC      118.88   103.25   222.13          1 
 Singapore  2018     11  CCC      278.67   217.75   496.42          1 
 Singapore  2018     11  CCC     -108.72   -94.75  -203.47          2 
 Singapore  2018     11  CCC           0        0        0          3 
 Singapore  2018     10  DDD      446.14    385.9   832.04          1 
 Singapore  2018     11  DDD      138.95      121   259.95          1 
 Singapore  2018     11  DDD     -228.07        0  -228.07          2 
 Singapore  2018     10  EEE      905.32    796.5  1701.82          1 
 Singapore  2018     10  EEE     -405.75  -357.75   -763.5          2 
 Singapore  2018     10  EEE       29.66        0    29.66          3 
 Singapore  2018     11  EEE       147.8      127    274.8          1 
 Singapore  2018     10  FFF           0        0        0          3 
 Singapore  2018     10  GGG           0        0        0          3 
 Singapore  2018     10  HHH      120.57    104.5   225.07          1 
 Singapore  2018     10  HHH           0        0        0          3 
 Singapore  2018     11  HHH      189.59   165.25   354.84          1 
 Singapore  2018     10  JJJ      117.12   100.25   217.37          1 
 Singapore  2018     10  JJJ           0        0        0          3 
 Singapore  2018     11  JJJ      291.53   254.25   545.78          1

and I am trying to calculate distinct 'CustID' based on 2 criteria which is in 3 columns:

  1. 'Sales' AND 'Points' cannot both be '0'
  2. 'OrderType' is only in (1, 2, 3)

Hence I created an additional column 'TestSUM' that sums 'Sales' and 'Points' which does not equal to 0, to fulfill criteria #1

With the code below, I would be able to exclude CustID FFF and GGG when doing distinct count of CustID based on my criteria #1:

Select 
Country
, o.Year
, o.Month
, sum(o.Sales) 
, sum(o.Points)
, sum(o.Sales + o.Points) as 'TestSUM'
, o.OrderType

From FactOrders O
Join CustTable as C on o.CustID = c.CustID
and o.OrderType in (1,2,3) 

Group by 
Country
, o.Year
, o.Month
, o.OrderType
, c.CustID

having sum(o.Sales + o.Points) <> 0   

Order by
c.CustID

However, how I want my results to look like is actually this:

  Country   Year  Month  Distinct Count   Sales  
 Singapore  2018     10               6  2048.06 
 Singapore  2018     11               7  1996.61

How do I amend my script to remove the column of "CustID", "Point", "TestSUM", "OrderType" but still maintain my distinct count reflective of the criteria

having sum(o.Sales + o.Points) <> 0

Details from comment:

I want sales+points <>0 criteria applied per customer, but the results returned to not show lines by CustID when i remove CustID, Point, TestSum and OrderType from the group by and select, the sales+points<>0 criteria does not apply per customer anymore... the distinct count of CustID would include lines where sales+points<>0

tsql sql-server sql
2 Answers

possibly you can do this like below. Note that I removed the JOIN to CustTable as it was not used. You can keep it back if you do not have referential integrity between FactOrders and CustTable.

I moved your having to where as you are counting customers even if they have one row which is 0 and 0.

See live demo

  Select 
     Country=o.Country
     ,Year= o.Year
     ,Month= o.Month
     ,[Distinct Count]=Count(distinct custid)
     ,Sales= sum(o.Sales) 
From FactOrders O
where 
    o.OrderType in (1,2,3) and (o.Sales<>0 or o.Points<>0)
Group by 
   o.Country, o.Year, o.Month
user_11232260

This produces your demo results.

Select 
     Country=o.Country
     ,Year= o.Year
     ,Month= o.Month
     ,[Distinct Count]=Count(distinct custid)
     ,Sales= sum(o.Sales) 
From FactOrders O
where 
    o.OrderType in (1,2,3) 
and o.Sales + o.Points<>0
Group by 
   o.Country, o.Year, o.Month

However, that does not satisfy your stated requirement where Sales and Points may not both be 0. If Sales = -10 and Points = 10, that will also add up to 0, even though neither equals 0.

The following should be what you're looking for:

Select 
     Country=o.Country
     ,Year= o.Year
     ,Month= o.Month
     ,[Distinct Count]=Count(distinct custid)
     ,Sales= sum(o.Sales) 
From FactOrders O
where 
    o.OrderType in (1,2,3) 
and o.Sales <> 0 and o.Points <> 0    --  because, -10 + 10 = 0
Group by 
   o.Country, o.Year, o.Month
user_16654370
Related