Select where Min >0

[CoffeeGuru]

I am trying to find the stores that sell the least amount of a product each week, when it is sold at least once.

I am using the Min of sales

with a where clause of >0 to elliminate 0 sales stores as in a year every store may fall in to the categeory.

Max works fine but Min is causing me problems

Any ideas

 

[pr2-eugin]

Not sure what exactly is not working mean, but I think it might be because you have some Null values in the table? Try something like..

SELECT Min(yourTableName.theFieldName) As MinOfFN
FROM yourTableName
WHERE ((yourTableName.theFieldName > 0) AND (yourTableName.theFieldName Is Not Null));

 

[namliam]

Well if you want to put a restriction on your sum/min/max, you shouldnt put it on your where but rather having clause of the query:

SELECT Min(yourTableName.theFieldName) As MinOfFN
FROM yourTableName
HAVING Min(yourTableName.theFieldName) > 0;

Not sure offhand how null values are handled here

 

[pr2-eugin]

Well if you want to put a restriction on your sum/min/max, you shouldnt put it on your where but rather having clause of the query:

SELECT Min(yourTableName.theFieldName) As MinOfFN
FROM yourTableName
HAVING Min(yourTableName.theFieldName) > 0;

Not sure offhand how null values are handled here

@namliam, Tried something with some sample data..

AgentID    dayTarget
1          5
2          2
3          0
4          3

The result for the above, using HAVING clause.

MinOfFN

The result using a WHERE,

MinOfFN
2

I have always had confusion of using the two clauses ! IMHO, WHERE filters the result before GROUPING takes place, HAVING takes effect after GROUPING. In the above example, The Min value corresponds to 0, so taking records that are > 0 would result in an empty recordset. However, if you remove the 0's before aggregating, we end up with 2, so there is no need to use a HAVING clause, thus we get the right value that is not equal to 0.

So using WHERE would be efficient, as it removes the data that is not required; also gets the right data.

**DISCLAIMER - I might be wrong !**

 

[namliam]

You are correct, where is applied to the column itself, Having is applied to the Grouping...

You cannot do (for example) Where Sum(x) > 0
That needs to be Having Sum(x) > 0

You could however do Where x > 0
But then again you cannot do Having x > 0

However if you have a column that you "group by" but dont apply any sum to you can put it in both...

Basicaly the Having is applied after the query is run, the where during (or before)

 

[CoffeeGuru]

Thanks Guys

Love the discussion.
Just for the record WHERE IS Not Null worked for me.
But I'll bear in mind that it may not always.