How could I add a WHERE condition to code with INNER JOINs?

Question

I don't know how to add a where condition for an AS column, is there a

I've tried using the column with square brackets and it doesn't work

SELECT   
    Suppliers.CompanyName,
    SUM([Order Details].UnitPrice*Quantity) AS [Total sales]
FROM
    [Order Details]  
INNER JOIN 
    Products ON products.ProductID = [Order Details].ProductID
INNER JOIN 
    Suppliers ON Products.SupplierID = Suppliers.SupplierID
WHERE 

GROUP BY 
    Suppliers.CompanyName
ORDER BY 
    2 DESC;

I would like to only see the total sales over 10,000

forpas · Accepted Answer · 2019-04-28T17:09:16.910

3

You need to add the condition in a HAVING clause:

SELECT  
  Suppliers.CompanyName, 
  SUM([Order Details].UnitPrice*Quantity)AS [Total sales]
FROM [Order Details] 
INNER JOIN 
Products ON products.ProductID= [Order Details] .ProductID
INNER JOIN
Suppliers ON Products.SupplierID= Suppliers.SupplierID
GROUP BY Suppliers.CompanyName
HAVING SUM([Order Details].UnitPrice*Quantity) > 10000
Order by 2 desc;

edited Apr 28 '19 at 17:09

answered Apr 28 '19 at 17:07

forpas

160,666
10
38
76

Copy/paste leftovers, Thanks. – forpas Apr 28 '19 at 17:09

Barbaros Özhan · Answer 2 · 2019-04-28T17:16:24.747

You just need to add a HAVING clause instead of a WHERE clause,

after GROUP BY as

HAVING SUM([Order Details].UnitPrice*Quantity) > 10000

in your SQL statement :

SELECT  Suppliers.CompanyName, SUM([Order Details].UnitPrice*Quantity) AS [Total sales]    
  FROM [Order Details]     
 INNER JOIN 
        Products ON products.ProductID= [Order Details] .ProductID
 INNER JOIN
        Suppliers ON Products.SupplierID= Suppliers.SupplierID
GROUP BY Suppliers.CompanyName
HAVING SUM([Order Details].UnitPrice*Quantity) > 10000
ORDER BY 2 desc

How could I add a WHERE condition to code with INNER JOINs?

2 Answers2