Table Pivot with AVG inside MAX(DECODE())

Hey guys I need help with the SQL below. It keeps giving me a single output for all of my a.names:

 SELECT Drink.name,  
        MAX(DECODE(Size.type, 'Small', avg, NULL)) Small,
        MAX(DECODE(Size.type, 'Medium', avg, NULL)) Medium,
        MAX(DECODE(Size.type, 'Large', avg, NULL)) Large
 FROM Drink, Size (
       开发者_如何转开发      SELECT avg(Size.price) avg, Size.type, Drink.name FROM Drink, Size 
             GROUP BY Size.type, Drink.name )
 GROUP BY Drink.name
 ORDER BY Drink.name;

OUTPUT: For example, I'll use drink brand name with a type of small, medium, large. I want the average of all the drinks in the stores across town.

Without using pivot

Drink       |   Size    |   Price
Dr. Pepper  |   Small   |    1.00
Dr. Pepper  |  Medium   |    1.50
Dr. Pepper  |   Large   |    2.00

Using pivot (the output I want):

Drink         |  Small    |   Medium  | Large
Dr. Pepper    |   1.00    |   1.50    |  2.00
Mountain Dew  |   0.50    |   0.75     |  1.25

The output I'm getting:

Drink         |  Small    |   Medium  | Large
Dr. Pepper    |   1.00    |   1.00    |  1.00
Mountain Dew  |   1.00    |   1.00    |  1.00

---

Use (Oracle 9i+):

  SELECT x.name,
         MAX(CASE WHEN x.type = 'Small' THEN x.avg END) AS small,
         MAX(CASE WHEN x.type = 'Medium' THEN x.avg END) AS Medium,
         MAX(CASE WHEN x.type = 'Large' THEN x.avg END) AS Large
    FROM (SELECT d.name,
                 s.type, 
                 AVG(s.price) avg
            FROM DRINK d
            JOIN SIZE s ON s.size_id = d.size_id
        GROUP BY d.name, s.type) x
GROUP BY x.name

Your information still lacks the JOIN criteria between the DRINK and SIZE tables - I made assumptions. Without the criteria, the query is just producing a cartesian product -- it will never produce the output you're expecting.

Using WITH clause (Oracle 9i+):

WITH sample AS (
   SELECT d.name,
          s.type, 
          AVG(s.price) avg
     FROM DRINK d
     JOIN SIZE s ON s.size_id = d.size_id
 GROUP BY d.name, s.type)
  SELECT x.name,
         MAX(CASE WHEN x.type = 'Small' THEN x.avg END) AS small,
         MAX(CASE WHEN x.type = 'Medium' THEN x.avg END) AS Medium,
         MAX(CASE WHEN x.type = 'Large' THEN x.avg END) AS Large
    FROM sample x
GROUP BY x.name

PIVOT/UNPIVOT

The ANSI syntax wasn't supported in Oracle until 11g.

---

Here's the solution that worked for me - thanks to OMG Ponies for clearing some things up.

SELECT x.NAME as "Drink", 
(MAX(DECODE(x.TYPE, 'Small', avg, NULL))) Small,
(MAX(DECODE(x."TYPE", 'Medium', avg, NULL))) Medium,
(MAX(DECODE(x."TYPE", 'Large', avg, NULL))) Large
FROM (
      SELECT Size."TYPE", Drink.NAME, Round(AVG(Size.price),2) avg 
      FROM Drink, Price 
      WHERE Drink.drink_id = Size.drink_id
      GROUP BY Size."TYPE", Drink.NAME) x
GROUP BY x.NAME
ORDER BY x.NAME;

I'm still not sure why JOIN on gave me the results that it did; I replaced using WHERE, and it worked... weird.