CODEWITHSUNDEEP
mysqlsqlsubquery
Babeeshka
Babeeshka

Reputation: 105

MySQL - Using subquery to find an average

I am having trouble with the syntax of this query. I am trying to return the largest total for one company from each country.

The tables look like:

Orders
 Columns
  OrderID
  CustomerID
  EmployeeID
  OrderDate
  RequiredDate

OrderDetails
 Columns
  OrderID
  ProductID
  UnitPrice
  Quantity

Products
 Columns
  ProductID
  ProductName
  QuantityPerUnit
  UnitPrice

Customers
 Columns
  CustomerID
  CompanyName
  ContactName
  Country

I have tried the following:

SELECT
    T1.Country,
    CompanyName,
    T1.OrderSum
FROM
    (SELECT
        C.Country,
        C.CompanyName,
        SUM(UnitPrice * Quantity) AS OrderSum
    FROM Customers C
        JOIN Orders O
            ON C.CustomerID = O.CustomerID
        JOIN OrderDetails D
            ON D.OrderID = O.OrderID
    GROUP BY C.Country) T1
            JOIN 
            -- TOP PAYMENT TOTALS BY COUNTRY
            (SELECT COUNTRY, 
                    MAX(OrderSum) AS OrderSum
             FROM
            -- PAYMENT TOTALS BY CUSTOMER
                (SELECT     C.Country,
                            C.CompanyName,
                            SUM(UnitPrice * Quantity) AS OrderSum
                 FROM Customers C
                    JOIN Orders O1
                        ON O1.CustomerID = C.CustomerID
                    JOIN OrderDetails D1
                        ON D1.OrderID = O1.OrderID
                 GROUP BY C.COUNTRY, C.CompanyName) T2
             GROUP BY COUNTRY) T3
        ON T1.COUNTRY = T3.COUNTRY
            AND T1.OrderSum = T3.OrderSum
ORDER BY Country;

This query only returns three countries:

Ireland Hungry Owl All-Night Grocers    57317.3900
Norway  Sant Gourmet    5735.1500
Poland  Wolski  Zajazd  3531.9500

But, this query I tried, returns all countries, but I am not sure if it is correct because I did not include the 'max' value as I did in the previous query:

SELECT
    T1.Country,
    CompanyName,
    T1.OrderSum
FROM
    (SELECT
        C.Country,
        C.CompanyName,
        SUM(UnitPrice * Quantity) AS OrderSum
    FROM Customers C
        JOIN Orders O
            ON C.CustomerID = O.CustomerID
        JOIN OrderDetails D
            ON D.OrderID = O.OrderID
    GROUP BY C.CompanyName) T1
GROUP By Country
ORDER BY Country;

I am also unsure if I am calculating the order totals correctly, which may be the mistake on my part. But I am trying to find the company from each country that has the largest order total. Sorry for all the text.

Upvotes: 0

Views: 73

Answers (1)

Leo C
Leo C

Reputation: 22439

The following query would list only the companies with the largest order total per country:

SELECT A.Country, A.CompanyName, A.OrderSum
FROM (
    SELECT
        C.Country,
        C.CompanyName,
        SUM(D.UnitPrice * D.Quantity) AS OrderSum
    FROM Customers C
        JOIN Orders O ON C.CustomerID = O.CustomerID
        JOIN OrderDetails D ON D.OrderID = O.OrderID
    GROUP BY
        C.Country, C.CustomerID
) A
JOIN (
    SELECT
        S.Country, MAX(S.OrderSum) as MaxSum
    FROM (
        SELECT
            C.Country,
            C.CompanyName,
            SUM(D.UnitPrice * D.Quantity) AS OrderSum
        FROM Customers C
            JOIN Orders O ON C.CustomerID = O.CustomerID
            JOIN OrderDetails D ON D.OrderID = O.OrderID
        GROUP BY
            C.Country, C.CustomerID
        ) S
    GROUP BY
        S.Country
) B ON A.Country = B.Country
WHERE
    A.Country = B.Country AND
    A.OrderSum = B.MaxSum
ORDER BY
    A.Country, A.CompanyName
;

[UPDATE]

Note that the above SQL follows the way OrderSum is calculated in your listed queries. Given that table Products has QuantityPerUnit and UnitPrice, I suspect that your OrderSum should be multiplied by QuantityPerUnit as well – in which case you'll need to revise the math for OrderSum.

Upvotes: 1

Related Questions