CODEWITHSUNDEEP
sqlsql-servert-sql
Nicklas
Nicklas

Reputation: 35

Select min returns empty row

Query

select min Price, min year from tblProduct

returns empty ("") results. I only want results in numbers. How do I do that?

Example:

tblProduct
Price   Year

1000    2008
2000    2009
500  2001
        2005
200  2000

The result is Price "", year 2000. I want the result to be Price 200, year 2000

Upvotes: 3

Views: 8195

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

This will give the minimum value for each year 

Select min(p.price) , year
  from 
    tblProduct p 
  WHERE p.price <> ''
  Group by 
      p.year

This will give you the year and price for the min Price.

This may result in multiple rows when more than one year has the min price

Select p.year, p.price from 
tblProduct p inner join 
    (Select min (Price) price
    from tblProduct where price is not null) minP
on p.price = minp.Price

Upvotes: 3

Vincent Savard
Vincent Savard

Reputation: 35927

First of all, what is your RDBMS? For the ones I tried (MySQL and PostgreSQL), MIN ignores NULL.

Therefore, I assume your column is of type varchar or text. Which doesn't make sense for a price, so I suggest that you change it to a numeric type.

If you don't want to, then I'd use something like this :

SELECT MIN(price), MIN(year)
FROM tblProduct
WHERE price <> ''

Which would return (200, 2000). But really, it doesn't make sense. You want to use a numeric type and you want to use NULL instead of using an empty string.

Upvotes: 3

Raymund
Raymund

Reputation: 7892

Try this one

SELECT     MIN(Price) AS Price, MIN(Year) AS Year
FROM         tblProduct
HAVING      (MIN(Price) IS NOT NULL) OR
                      (MIN(Price) <> '')

Upvotes: 3

Justin Swartsel
Justin Swartsel

Reputation: 3431

MIN, MAX, SUM, AVG and certain other aggregates will return NULL if a NULL exists in the data set. You want to use:

SELECT ISNULL(MIN(Price),0) as MinPrice, ISNULL(MIN(Year),0) as MinYear FROM tblProduct

Upvotes: 0

Related Questions