CODEWITHSUNDEEP
sqloracle-database
shellythatsme
shellythatsme

Reputation: 43

SQL Omitting rows that only appear once

OK i'm very new to SQL so here's the question:

For each salesrep that has taken an order, list the minimum, maximum and average order amount for all their orders. Include only those orders made anytime from 1990-1999 inclusive. Omit from the list any salesrep that has only made 1 order in this time frame. Sort the results by Salesrep number.

And I got it down to :

SELECT Rep
      ,MIN(Amount) AS "MinAmount"
      ,MAX(Amount) AS "MaxAmount"
      ,AVG(Amount) AS "AverageAmount"
FROM Orders
WHERE Orderdate BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY Rep;

I thought the anwser was:

AND Rep > ( SELECT COUNT(Distinct Rep)
              FROM Orders)

But it doesn't seem to work. Any help is appreciate!

Lastly Orders Contains:

CREATE TABLE Orders
(OrderNum    NUMBER(10)      NOT NULL,
 OrderDate   DATE            NOT NULL,
 Cust        NUMBER(10),
 Rep         NUMBER(10),
 Mfr         CHAR(3)         NOT NULL,
 Product     CHAR(5)         NOT NULL,
 Qty         NUMBER(5)       NOT NULL,
 Amount      NUMBER(9,2)     NOT NULL,
 CONSTRAINT OrdersPK 
      PRIMARY KEY (OrderNum));

Upvotes: 2

Views: 256

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can solve the problem with a subquery, but the right subquery is:

AND Rep IN (SELECT Rep
            FROM Orders
            GROUP BY Rep
            HAVING count(*) > 1
           )

To give some intuition as to why your approach won't work, think about what Rep is. It is just a code. So, doing a > then on a code isn't going to provide much of interest. At least, whatever it does is unlikely to be related to the number of orders. Even if you really, really want it to.

An easier way is to dispense with that clause altogether and just add:

HAVING count(*) > 1

Upvotes: 6

user2989408
user2989408

Reputation: 3137

I think what you need is HAVING. The HAVING COUNT(OrderNum) > 1 filters out all Reps that have made less than one order in the period. Some examples of having clause are shown here.

SELECT Rep ,
    MIN(Amount) AS "MinAmount" ,
    MAX(Amount) AS "MaxAmount" ,
    AVG(Amount) AS "AverageAmount" 
FROM Orders 
WHERE Orderdate BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY Rep
HAVING COUNT(OrderNum) > 1;

Upvotes: 4

Related Questions