Item without price, or duplicate price

[Auto]

hi everyone,



i have a table called Books, in that table there is column ChapterName, Auther, ITEM, Price.
each book has a item number, and each book has a few records with the same data, just the first column is diffrent where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price

now i want a report where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?
i am breaking my head all day with it, and i cant come up with anything.

[Dal Jeanis]

This will get items with more than one non-Null price:

Select ITEM, Count(Price)
FROM Books
GROUP BY ITEM
Having Count(Price) > 1;

This will get items with no non-null prices.

Select B1.ITEM
FROM Books AS B1
WHERE B1.ITEM NOT IN 
   (
   SELECT ITEM
   FROM Books AS B2
   GROUP BY ITEM
   Having Count(B2.Price) > 0
   );

[Auto]

Brilliant! this is exactly what i want, thank you so much, WOW

[Auto]

the first SQL for more than one-null price worked like a charm,
but this code hangs down and freeze

This will get items with no non-null prices.

Select B1.ITEM
FROM Books AS B1
WHERE B1.ITEM NOT IN 
   (
   SELECT ITEM
   FROM Books AS B2
   GROUP BY ITEM
   Having Count(B2.Price) > 0
   );

[/QUOTE]

[Auto]

ok, here is what i did, i used the same SQL for non null price, just changed <

Select ITEM, Count(Price)
FROM BooksGROUP BY ITEM Having Count(Price) < 1;

and its doing the purpose!

[Dal Jeanis]

Glad you go what you needed. Not sure what hung the system about that second one.

This would work too

Select B1.ITEM
FROM Books AS B1
WHERE B1.ITEM NOT IN 
    (
    SELECT ITEM
    FROM Books AS B2
    WHERE B2.Price >= 0
    );