CODEWITHSUNDEEP
sqlsql-serverinner-joinwhere-clause
CodingHelp123
CodingHelp123

Reputation: 41

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

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

Upvotes: 1

Views: 55

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

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

Upvotes: 1

forpas
forpas

Reputation: 164089

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;

Upvotes: 3

Related Questions