-1

I am not accustomed to using SQL for View Creation, It's forcing me to use HAVING and GROUP BY, in a standard SQL statement i would use WHERE but apparently that's not allowed. I want to SELECT DISTINCT on dbo.[ShowTex Belgie NV$Item].No_ only. But it is also combining anything i add in GROUP BY it seems. I can't use HAVING on columns unless i include them in GROUP BY. Getting this far has already taken me ages. :/ I just wanted to create a simple WHERE statement ;( 

SELECT DISTINCT TOP (100) PERCENT dbo.[ShowTex Belgie NV$Item].No_, SUM(dbo.[ShowTex Belgie NV$Warehouse Entry].Quantity) AS [Quantity Sum]
FROM            dbo.[ShowTex Belgie NV$Item] 
LEFT OUTER JOIN dbo.[ShowTex Belgie NV$Warehouse Entry] ON dbo.[ShowTex Belgie NV$Item].No_ = dbo.[ShowTex Belgie NV$Warehouse Entry].[Item No_]
GROUP BY dbo.[ShowTex Belgie NV$Item].No_, dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code]
HAVING        (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'SHIPPING') OR
                     (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'VERZEND') OR
                     (dbo.[ShowTex Belgie NV$Warehouse Entry].[Bin Code] <> 'WORKSHOP')
ORDER BY dbo.[ShowTex Belgie NV$Item].No_

Using Server Studio Managment 2012 but there doesnt seem to be a hashtag for that.

As based on Gordon Linoff's Answer my SQL query became 

SELECT        TOP (100) PERCENT i.No_, SUM(we.Quantity) AS [Quantity Sum]
FROM            dbo.[ShowTex Belgie NV$Item] AS i LEFT OUTER JOIN
                     dbo.[ShowTex Belgie NV$Warehouse Entry] AS we ON i.No_ = we.[Item No_]
WHERE        (we.[Bin Code] NOT IN ('SHIPPING', 'VERZEND', 'WORKSHOP'))
GROUP BY i.No_
ORDER BY i.No_
WouldBeNerd
  • 629
  • 11
  • 29

1 Answers1

1

First, why are you using distinct and group by? Second, what is wrong with this version:

SELECT i.No_, SUM(dbo.we.Quantity) AS [Quantity Sum]
FROM dbo.[ShowTex Belgie NV$Item] i LEFT OUTER JOIN
     dbo.[ShowTex Belgie NV$Warehouse Entry] we
     ON i.No_ = we.[Item No_]
WHERE we.[Bin Code] NOT IN ('SHIPPING', 'VERZEND', 'WORKSHOP')
GROUP BY i.No_, we.[Bin Code]
ORDER BY i.No_;

Table aliases also make the query much more readable.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks For the quick reply, Aliases do make it much more readable; thanks a lot for that. Missing one bracket at the end of 'WORKSHOP') . I'm getting too many entries though so something is still wrong. I want one row for each item Number("No_") in the "Item" List thats why im trying to use DISTINCT on the first column only. – WouldBeNerd Aug 04 '15 at 14:13
  • Got it , Many Thanks Gordon. SQL is not my forté. Was unaware that Group By and Distinct are the same. – WouldBeNerd Aug 04 '15 at 15:16