SYNTAX for two COUNT questions?-CodePudding

I am looking for the syntax to a) Count the number of sales by each person if they had less than 3 sales for the past period. I used [SELECT COUNT(SalesAmount),SalesPerson FROM SALES1 WHERE SalesAmount = < 3;] which is wrong.

I am also looking for the syntax to b) Find the number (count) of sales by each person, but only if they made less than or equal to £300 worth of sales for the past period.

Any help is appreciated, thank you!

CodePudding user response：

SELECT SalesPerson, SUM(SalesAmount), count(*) as NumberSales
 FROM SALES1 
GRUOP BY SalesPerson    HAVING count(*) < 3;

  SELECT SalesPerson, SUM(SalesAmount), count(*) as NumberSales
 FROM SALES1 
GRUOP BY SalesPerson    HAVING SUM(SalesAmount) <= 300;

CodePudding user response：

What you need is to pre-query per sales person on the week basis withouto respect to the why. Only after that, this will become the basis of the secodary qualifications you are asking. Also, what defines a "period"? Is it per week, per month? With such low numbers, I will assume it is per week from your data.

So my alias PQ (pre-query) is doing the aggregation per sales person per week. That is used for the final

select
      sum( case when PQ.weekCount <= 3 then 1 else 0 end ) CountLessThan3,
      sum( case when PQ.weekSales <= 300 then 1 else 0 end ) SalesLessThan300
   from
      ( select
              salesPerson,
              week,
              count(*) weekCount,
              sum( salesAmount ) weekSales
           from
              YourTable
           group by
              salesPerson,
              week ) PQ

Now, if you wanted to know who qualified under these low performing numbers, you might want to alter to something like below using a HAVING clause which is applied AFTER the group by condition

select
      salesPerson,
      week,
      count(*) weekCount,
      sum( salesAmount ) weekSales
   from
      YourTable
   group by
      salesPerson,
      week
   having
         count(*) <= 3
      OR sum( salesAmount ) <= 300