What is actually going on with HAVING clause here?

Question

Here is the SQL Fiddle link.

According to my understanding, GROUP BY would return 2 tuples.

MODEL                        ITEM_TYPE                             PRICE
---------------------------- ------------------------------------- ----------
010-99-0101                  BACKPACK                              5329.1 
626-21-1500                  BACKPACK                              1485.86 

The value of average price is,

AVG(PRICE)
----------
4858.014444 

So, The following query should filter out the smaller value of price.

SELECT      item_type, MODEL, items.price
FROM        ITEMS
WHERE       MANUFACTURER_NAME = 'UWZ' 
AND         ITEM_TYPE = 'BACKPACK'
GROUP   BY item_type, items.price, MODEL
HAVING      ITEMS.PRICE > AVG(ITEMS.PRICE);

So, the output should be:

MODEL                        ITEM_TYPE                             PRICE
---------------------------- ------------------------------------- ----------
010-99-0101                  BACKPACK                              5329.1 

But, in reality, the following is the output:

Output

no rows selected

Answer 1

Think this query does the job you were intending:

SELECT MODEL, ITEM_TYPE, PRICE
FROM ITEMS
WHERE MANUFACTURER_NAME = 'UWZ'
  AND ITEM_TYPE = 'BACKPACK'
  AND PRICE > 
      (SELECT AVG(ITEMS.PRICE)
       FROM   ITEMS
       WHERE  MANUFACTURER_NAME = 'UWZ' 
       AND    ITEM_TYPE = 'BACKPACK');

SQL Fiddle demo

Explanation

Your attempted use of GROUP BY isn't having the desired effect. You can see this in this modification of the posted query - the HAVING clause has been removed and a column is added to count the number of items in each group: only 1 for each. GROUP BY can be used to calculate an average but once this average has been calculated it's the individual rows that need to be picked out - which can't be done when they've been aggregated into a group. Hence two separate operations are needed - an aggregation function to find the average and a select to pick out the rows that are of interest.

Answer 2

The HAVING clause in your current query is fetching rows where the price for that row is greater than the average price for that row only.

The average of a single number is equal to the number, so no rows are returned.

You can test this by changing the HAVING clause to ITEMS.PRICE = AVG(ITEMS.PRICE) - this will return all rows.

Window functions are handy for this kind of query. The OVER clause below specifies that AVG_PRICE applies the average to all rows for a given Manufacturer and Item Type.

select  * 
from    (
    select      MODEL, 
                ITEM_TYPE, 
                PRICE, 
                avg(PRICE) over( 
                   partition by MANUFACTURER_NAME, ITEM_TYPE 
                ) as AVG_PRICE
    from        ITEMS
    ) as ITEMS_WITH_AVG
where  PRICE > AVG_PRICE

See amended SQLfiddle

You might also want to consult the docs on window/analytic functions in Oracle.

Answer 3

Assuming that you won't always want to return just the records for 'UWZ' and 'BACKPACK' you could use the query below, which uses a subquery (derived table). It builds on the same principles as explained in Steve Chambers answer, so I won't repeat the explanation.

You can uncomment the additional conditions in the WHERE clause to return your original answer.

SELECT MODEL
     , ITEMS1.ITEM_TYPE
     , ITEMS1.MANUFACTURER_NAME
     , PRICE
FROM ITEMS ITEMS1
    INNER JOIN (SELECT ITEM_TYPE
                     , MANUFACTURER_NAME
                     , AVG(PRICE) AVG_PRICE
                FROM   ITEMS
                GROUP BY ITEM_TYPE
                     , MANUFACTURER_NAME) ITEMS2 
        ON ITEMS1.ITEM_TYPE = ITEMS2.ITEM_TYPE
            AND ITEMS1.MANUFACTURER_NAME = ITEMS2.MANUFACTURER_NAME       
WHERE PRICE > AVG_PRICE
    --AND ITEMS1.MANUFACTURER_NAME = 'UWZ'
    --AND ITEMS1.ITEM_TYPE = 'BACKPACK'

Answer 4

You need to change your query to the following:

SELECT      Distinct MODEL, item_type, price
FROM        ITEMS
WHERE       MANUFACTURER_NAME = 'UWZ' 
AND         ITEM_TYPE = 'BACKPACK'
And         Price > (Select Avg(Price) From Items)

If you want to keep the GROUP BY for some reason, you can do this instead:

SELECT      MODEL, item_type, items.price
FROM        ITEMS
WHERE       MANUFACTURER_NAME = 'UWZ' 
AND         ITEM_TYPE = 'BACKPACK'
GROUP BY    item_type, items.price, MODEL
Having      Price > (Select Avg(Price) From Items)

The reason your query wasn't returning any results was because the AVG(ITEMS.PRICE) is over the GROUP BY, so this would return 5329.1. And 5329.1 is neither greater than, nor less than (your original query before the edit) 5329.1. Thus, you get no results.

From what I gather, you are looking for those that are greater than the AVG of all items in the table, which can be achieved via the queries above.

You can check this for yourself with the following:

SELECT      item_type, MODEL, items.price, AVG(ITEMS.PRICE) As average
FROM        ITEMS
WHERE       MANUFACTURER_NAME = 'UWZ' 
AND         ITEM_TYPE = 'BACKPACK'
GROUP   BY  item_type, items.price, MODEL

Results:

item_type   MODEL       price   average
------------------------------------------------
BACKPACK    010-99-0101 5329.1  5329.1

Answer 5

You are grouping by item_type, price, and model. That means you get one result line per item_type, price, and model. For example one line for item_type = 'BACKPACK' and price = 3915.73 and model = '172-50-2742'. Are there even two records for the same item_type, price, and model in your table? For this would be the only reason for grouping them.

For each such line you want to show item_type, model, and price, which is just the three columns you are grouping by. No other information, e.g. how many rows match these item_type, price, and model, or how many items are in stock for this combination. You would have got the same result with

select distinct item_type, model, price

Then to your having clause: price shall be greater then avg(price) for a group. Let's again take the group with price = 3915.73. The price is 3915.73 and the avarage price of all 3915.73 prices is also 3915.73 of course. You could just as well have written having price > price. The price is never greater than itself of course and the criteria is never met. You get no result rows accordingly.

The average price of 4858.014444 you name is the avarage over all records in the table. So you want to select all records UWZ/BACKPACK records with a higher price than this?

select *
from items
where manufacturer_name = 'UWZ' 
and item_type = 'BACKPACK'
and price > (select avg(all_items.price) from items all_items);

It seems strange though that you compare with the avarage over all products. It would seem more natural to compare with all BACKPACKs. In order to do this, you'd have to add this criteria to your subquery.