CODEWITHSUNDEEP
sqlgroup-bycommon-table-expressionpartition-by
adhocEY
adhocEY

Reputation: 75

How to Filter Data in SQL Without Using Group By / Having Functions

Have a situation where I'm trying to produce a query that shows the item # on a sales order and the total outstanding quantity across all orders. I can do this as follows:

SELECT 
SL.[Item No_], 
SUM(SL.[Outstanding Quantity]) 

FROM [Database$Sales Header] SH
LEFT JOIN [Database$Sales Line] SL ON SL.[Document No_] = SH.[No_]
LEFT JOIN [Database$Items] I ON I.[No_] = SL.[Item No_]

GROUP BY 
SL.[Item No_], 
SH.[Document Type], 
I.[Product Code]

HAVING
SH.[Document Type] = '1'
AND I.[Product Code] = 'SHIRT'

ORDER BY
SL.[Item No_]

Above code gives me a simple summary of item # and qty. on all sales orders. I'm using the HAVING clause to include only sales orders (Document Type) and only items that are shirts (Product Code).

The issue I'm having is when I want to exclude a particular customer.

I tried adding: AND SH.[Customer No_] <> 'CUST1' to the HAVING clause but if I do that, then SQL will require me to add it in the GROUP BY clause. The result is I get duplicate rows where it was summarized before because now SQL is reporting on outstanding quantity by item # and customer # which is not what I want.

So I am not sure how to exclude that customer without putting it into GROUP BY.

Upvotes: 1

Views: 727

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Please use where clause to filter the data.

SELECT 
SL.[Item No_], 
SUM(SL.[Outstanding Quantity]) 

FROM [Database$Sales Header] SH
LEFT JOIN [Database$Sales Line] SL ON SL.[Document No_] = SH.[No_]
LEFT JOIN [Database$Items] I ON I.[No_] = SL.[Item No_]

where SH.[Customer No_] <> 'CUST1' 

GROUP BY 
SL.[Item No_], 
SH.[Document Type], 
I.[Product Code]

HAVING
SH.[Document Type] = '1'
AND I.[Product Code] = 'SHIRT'

ORDER BY
SL.[Item No_]

Upvotes: 1

Related Questions