MYSQL CASE Statement with aggregate functions only returning one line-CodePudding

I have problem with this query it only returns one row. I am using the database located here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SELECT CustomerName, TotalPrice,
       (CASE TotalPrice
          WHEN TotalPrice =  MIN(TotalPrice) THEN 'Lowest Sales Potential'
          WHEN TotalPrice =  MAX(TotalPrice) THEN 'Highest Sales Potential'
          WHEN TotalPrice > AVG(TotalPrice) THEN 'High Sales Potential'
          ELSE 'Average Sales Potential'
        END) AS Potential
FROM
(
    SELECT CustomerName, SUM(Price*Quantity) AS TotalPrice
    FROM Orders
      INNER JOIN OrderDetails ON OrderDetails.OrderID = Orders.OrderID
      INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
      INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
    GROUP BY CustomerName
)

CodePudding user response：

What you need is TWO queries in the from... First with the total sales per customer, and a completely additional query that only returns the min/max/avg as one record. Then you can apply the comparison as you are looking for.

To get the total per customer will also need to be used exact same to get the lowest, avg and max for final comparison. So this process I will use with WITH CTE construct. CTE is a common table expression. So, you can write a query BEFORE the actual query you want and that alias will be recognized within the final query. This way you dont have to write the entire query down each time... You will see shortly.

Also, I am applying "alias" names to the query to shorten readability. Also good to identify all parts as table.column or alias.column so people know which table the data is coming from that dont know your structures. I am assuming the CustomerName comes from the customer table and I am using the alias "c", so c.CustomerName. As for Price and Quantity, I would also assume those come from the order detail as prices can change for inventory, but a product itself is relatively static and a one-time entry. So Price and Quantity are using the "od" alias from OrderDetail. If your table structure has the price in the product table, just change that alias reference to "p".

I also find indentation to see where tables are coming from and linking to helps see the individual components of what part of a query relies on the other and hope it helps you too.

WITH SumByCustomerQry as
( SELECT 
        c.CustomerName, 
        SUM( od.Price * od.Quantity ) TotalPrice
     FROM 
        Orders o
           JOIN OrderDetails od
              ON o.OrderID = od.OrderID
              JOIN Products p
                 ON od.ProductID = p.ProductID
           JOIN Customers c
              ON o.CustomerID = c.CustomerID
           GROUP BY 
              CustomerName 
) 

SELECT 
      sbc.CustomerName, 
      sbc.TotalPrice,
      CASE when sbc.TotalPrice = agg.MinTotal THEN 'Lowest Sales Potential'
           when sbc.TotalPrice = agg.MaxTotal THEN 'Highest Sales Potential'
           when sbc.TotalPrice > agg.AvgTotal THEN 'Above Average Sales'
           else 'below average Sales Potential'
        END Potential
   FROM
      -- first, getting all individual customers
      SumByCustomerQry sbc
         -- now, we need the same pre-aggregated result to get the min/max/avg
         JOIN
         ( select 
                 min( TotalPrice ) MinTotal,
                 max( TotalPrice ) MaxTotal,
                 avg( TotalPrice ) AvgTotal
              from
                 -- notice using the same pre-aggregate query
                 SumByCustomerQry ) Agg
           -- the result "Agg" query will return 1 record, so 
           -- just joining on 1=1 which is always true
           on 1=1

CodePudding user response：

If you have MySQL 8.0 (or MariaDB 10.2.2) or better, you can use CTE and window functions, as follows:

The fiddle without data

WITH aggr AS (
      SELECT CustomerName
           , SUM(Price*Quantity) AS TotalPrice
        FROM Orders
        JOIN OrderDetails
          ON OrderDetails.OrderID = Orders.OrderID
        JOIN Products  ON OrderDetails.ProductID = Products.ProductID
        JOIN Customers ON Orders.CustomerID      = Customers.CustomerID
       GROUP BY CustomerName
     )
SELECT CustomerName
     , TotalPrice
     , CASE WHEN TotalPrice = MIN(TotalPrice) OVER w THEN 'Lowest Sales Potential'
            WHEN TotalPrice = MAX(TotalPrice) OVER w THEN 'Highest Sales Potential'
            WHEN TotalPrice > AVG(TotalPrice) OVER w THEN 'High Sales Potential'
            ELSE 'Average Sales Potential'
        END AS Potential
  FROM aggr
WINDOW w AS ()
 ORDER BY TotalPrice DESC, CustomerName
;

Results:

 ------------------------------------ ------------ ------------------------- 
| CustomerName                       | TotalPrice | Potential               |
 ------------------------------------ ------------ ------------------------- 
| Ernst Handel                       |   35631.21 | Highest Sales Potential |
| MÃ¨re Paillarde                    |   23362.60 | High Sales Potential    |
| Save-a-lot Markets                 |   22500.06 | High Sales Potential    |
| Rattlesnake Canyon Grocery         |   18421.42 | High Sales Potential    |
| QUICK-Stop                         |   18178.80 | High Sales Potential    |
| Queen Cozinha                      |   17880.60 | High Sales Potential    |
| Piccolo und mehr                   |   16040.75 | High Sales Potential    |
| Hungry Owl All-Night Grocers       |   15391.02 | High Sales Potential    |
| Blondel pÃ¨re et fils              |   15253.75 | High Sales Potential    |
| Simons bistro                      |   14619.00 | High Sales Potential    |
| Frankenversand                     |   13384.32 | High Sales Potential    |
| Split Rail Beer & Ale              |   11114.02 | High Sales Potential    |
| Old World Delicatessen             |   10744.50 | High Sales Potential    |
| SuprÃªmes dÃ©lices                 |    8051.30 | High Sales Potential    |
| Bottom-Dollar Marketse             |    7963.75 | High Sales Potential    |
| Seven Seas Imports                 |    7438.70 | High Sales Potential    |
| LILA-Supermercado                  |    7358.68 | High Sales Potential    |
| Richter Supermarkt                 |    5875.75 | High Sales Potential    |
| Wartian Herkku                     |    5872.50 | High Sales Potential    |
| Berglunds snabbkÃ¶p                |    5406.90 | High Sales Potential    |
| Bon app'                           |    5256.35 | High Sales Potential    |
| Eastern Connection                 |    5017.09 | Average Sales Potential |
| Die Wandernde Kuh                  |    4841.90 | Average Sales Potential |
| La maison d'Asie                   |    4826.75 | Average Sales Potential |
| White Clover Markets               |    4416.00 | Average Sales Potential |
| Lehmanns Marktstand                |    4384.45 | Average Sales Potential |
| HILARIÃ“N-Abastos                  |    4320.50 | Average Sales Potential |
| Folk och fÃ¤ HB                    |    4313.90 | Average Sales Potential |
| Tortuga Restaurante                |    4269.40 | Average Sales Potential |
| Hanari Carnes                      |    4073.25 | Average Sales Potential |
| Que DelÃcia                        |    4049.10 | Average Sales Potential |
| TradiÃ§Ã£o Hipermercados           |    3949.25 | Average Sales Potential |
| Vaffeljernet                       |    3251.85 | Average Sales Potential |
| Magazzini Alimentari Riuniti       |    3240.90 | Average Sales Potential |
| Wellington Importadora             |    3020.00 | Average Sales Potential |
| ComÃ©rcio Mineiro                  |    2713.85 | Average Sales Potential |
| Princesa Isabel Vinhoss            |    2514.00 | Average Sales Potential |
| Chop-suey Chinese                  |    2249.00 | Average Sales Potential |
| KÃ¶niglich Essen                   |    2078.75 | Average Sales Potential |
| Folies gourmandes                  |    2030.20 | Average Sales Potential |
| Around the Horn                    |    1723.75 | Average Sales Potential |
| Familia Arquibaldo                 |    1658.20 | Average Sales Potential |
| Furia Bacalhau e Frutos do Mar     |    1656.00 | Average Sales Potential |
| Ricardo Adocicados                 |    1596.00 | Average Sales Potential |
| Godos Cocina TÃpica                |    1555.00 | Average Sales Potential |
| Morgenstern Gesundkost             |    1503.60 | Average Sales Potential |
| Ottilies KÃ¤seladen                |    1492.50 | Average Sales Potential |
| GROSELLA-Restaurante               |    1377.10 | Average Sales Potential |
| SantÃ© Gourmet                     |    1323.60 | Average Sales Potential |
| Gourmet Lanchonetes                |    1275.00 | Average Sales Potential |
| BÃ³lido Comidas preparadas         |    1227.50 | Average Sales Potential |
| Island Trading                     |    1127.00 | Average Sales Potential |
| Hungry Coyote Import Store         |    1104.25 | Average Sales Potential |
| Reggiani Caseifici                 |    1025.20 | Average Sales Potential |
| Victuailles en stock               |    1020.50 | Average Sales Potential |
| Romero y tomillo                   |     926.69 | Average Sales Potential |
| Lonesome Pine Restaurant           |     890.50 | Average Sales Potential |
| Pericles Comidas clÃ¡sicas         |     851.16 | Average Sales Potential |
| Vins et alcools Chevalier          |     825.60 | Average Sales Potential |
| Consolidated Holdings              |     790.00 | Average Sales Potential |
| Toms SpezialitÃ¤ten                |     710.50 | Average Sales Potential |
| Drachenblut Delikatessend          |     667.00 | Average Sales Potential |
| B's Beverages                      |     599.25 | Average Sales Potential |
| GalerÃa del gastrÃ³nomo            |     593.00 | Average Sales Potential |
| Wolski                             |     573.75 | Average Sales Potential |
| Wilman Kala                        |     566.00 | Average Sales Potential |
| Antonio Moreno TaquerÃa            |     504.00 | Average Sales Potential |
| LINO-Delicateses                   |     500.00 | Average Sales Potential |
| The Big Cheese                     |     421.00 | Average Sales Potential |
| OcÃ©ano AtlÃ¡ntico Ltda.           |     399.00 | Average Sales Potential |
| Du monde entier                    |     336.00 | Average Sales Potential |
| Centro comercial Moctezuma         |     126.00 | Average Sales Potential |
| Ana Trujillo Emparedados y helados |     111.00 | Average Sales Potential |
| Franchi S.p.A.                     |      62.46 | Lowest Sales Potential  |
 ------------------------------------ ------------ ------------------------- 
74 rows in set (0.004 sec)