Table products (id, price, description).
Some values:
1, 25.00, jacket
2, 110.00, jacket
3, 32.00, pants
4, 5.00, hat

I would like to extract the min/max(price) and the total of products
with the same name (jacket) in only one result, in example:

2 results found from 25.00 to 110.00

it's possible?

My query:
SELECT COUNT(*) AS tot, MIN(price) AS lowprice, MAX(price) AS highprice
FROM products
WHERE description LIKE '%jacket%'

But total is wrong because total is 4, where is the error?

Thanks

Max
--
http://www.maxori.it
L'Arte è la capacità di fare cose di nessun valore
e di riuscire a venderle
F. Zappa

The query was correct, in the database was present 1 product three times

Max
--
http://www.maxori.it
L'Arte è la capacità di fare cose di nessun valore
e di riuscire a venderle
F. Zappa

On Mar 1, 6:55*am, mac... (AT) nospam (DOT) it (MacMax) wrote:


I think what he meant to say is that he is looking for the total
number of products AND the min price AND the max price.
My Mysql system is down, so I tried this in Oracle:

create table abc (id number,price float,descrip varchar2(100));
insert into abc values (1,25,'jacket');
insert into abc values (2,100,'jacket');
insert into abc values (1,75,'hat');
insert into abc values (1,95,'gloves');

select count(*) as total,min(price),max(price) from abc;


TOTAL MIN(PRICE) MAX(PRICE)
4 25 100

4 products ranging in price from 25 to 100

and if you want to be really fancy you can use concat or || to have
the db produce the output...

select 'There ' ||case when count(*) = 1 then ' is ' else ' are ' end

RESULTS
There are 4 products ranging in price from $25.00 to $100.00


select 'There ' ||case when count(*) = 1 then 'is ' else 'are ' end ||
count(*)

RESULTS
There is 1 product ranging in price from $95.00 to $95.00


Now, if I want the total number of EACH product description it would
look more like:

select descrip,count(*) as total,min(price) as min,max(price) as max
from abc group by descrip;

DESCRIP TOTAL MIN MAX
hat 1 75 75
gloves 1 95 95
jacket 2 25 100