CODEWITHSUNDEEP
sql-server
Kevin
Kevin

Reputation: 39

WHERE by SUM in SELECT

I need help to resolve how I can use SUM() my Qty in WHERE clause.

Like this:

I have some clause if the Qty are under 0 And another clause if the Qty are over 0.

I'm not sure how to solve - can you help me on the right way?

SELECT 
    BC.[Location Code]
    ,BC.[Bin Code]
    ,BC.[Item No_]
    ,I.[Description]
    ,I.[Description 2]
    ,CAST(ROUND(ISNULL(SUM(C.[Quantity]), 0),0) AS Int) AS Qty

  FROM [Navision4].[dbo].[Selek Danmark$Bin Content] BC

  INNER JOIN [Navision4].[dbo].[Selek Danmark$Item] I ON I.No_ = BC.[Item No_]

 LEFT OUTER JOIN [Navision4].[dbo].[Selek Danmark$Warehouse Entry] C on C.[Bin Code] = BC.[Bin Code] AND C.[Item No_] = BC.[Item No_]


  WHERE 

  (
      (
      BC.[Bin Code] LIKE 'A%' OR
      BC.[Bin Code] LIKE 'B%' OR
      BC.[Bin Code] LIKE 'C%' OR
      BC.[Bin Code] LIKE 'D%' OR
      BC.[Bin Code] LIKE 'E%' OR
      BC.[Bin Code] LIKE 'F%' OR
      BC.[Bin Code] LIKE 'G%' OR
      BC.[Bin Code] LIKE 'H%' OR
      BC.[Bin Code] LIKE 'I%' OR
      BC.[Bin Code] LIKE 'J%' OR
      BC.[Bin Code] LIKE 'K%' OR
      BC.[Bin Code] LIKE 'L%' OR
      BC.[Bin Code] LIKE 'M%' OR
      BC.[Bin Code] LIKE 'N%' OR
      BC.[Bin Code] LIKE 'O%' OR
      BC.[Bin Code] LIKE 'P%' OR
      BC.[Bin Code] LIKE 'Q%' OR
      BC.[Bin Code] LIKE 'R%' OR
      BC.[Bin Code] LIKE 'S%' OR
      BC.[Bin Code] LIKE 'T%' OR
      BC.[Bin Code] LIKE 'U%' OR
      BC.[Bin Code] LIKE 'W%' OR
      BC.[Bin Code] LIKE 'X%' OR
      BC.[Bin Code] LIKE 'Y%' OR
      BC.[Bin Code] LIKE 'Z%' OR
      BC.[Bin Code] LIKE 'Æ%' OR
      BC.[Bin Code] LIKE 'Ø%' OR
      BC.[Bin Code] LIKE 'Å%' 
      )

  AND BC.[Bin Code] <> 'DS'
 
 AND Qty > 0
  
 )
 OR(
Qty < 0
)


  GROUP BY BC.[Location Code], BC.[Bin Code], BC.[Item No_], I.Description, I.[Description 2]

  ORDER BY BC.[Location Code], BC.[Bin Code], BC.[Item No_]

Maybe its possible with HAVING somehow?

Upvotes: 0

Views: 43

Answers (2)

NajiMakhoul
NajiMakhoul

Reputation: 1716

  GROUP BY BC.[Location Code], BC.[Bin Code], BC.[Item No_], I.Description, I.[Description 2]
  HAVING CAST(ROUND(ISNULL(SUM(C.[Quantity]), 0),0) AS Int) < 0
  ORDER BY BC.[Location Code], BC.[Bin Code], BC.[Item No_]

Upvotes: 1

izzy
izzy

Reputation: 807

You answered your own question, you do indeed use HAVING. It goes after the GROUP BY but before ORDER BY.

Upvotes: 1

Related Questions