Help with COUNT in SELECT Statement

[John Furphy]

Could someone assist with getting the count function working correctly
in this example please. I know the count function will return all rows
that do not have null values, but in this case I want to count all the
rows except those with a zero sale price, (which are unsold).

The table shows works offered for sale by an artist, with a positive
figure under SalePrice indicating a sale, and I want to count the
number sold by each auction house, and sum the sale price by auction
house. The table is as follows:

Name SalePrice Auction
Dowling 12000 Christies
Dowling 0 Christies
Dowling 10000 Christies
Dowling 0 Christies
Dowling 0 Christies
Dowling 6000 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys
Dowling 8000 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys

When I run this query:

SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
FROM MyTable
GROUP BY MyTable.Name, MyTable.Auction
HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));

The results are:

Name Number TotalSales Auction
Dowling 5 22000 Christies
Dowling 7 14000 Sothebys

The TotalSales is correct, but the Number (Count) is incorrect, as the
rows with zero were also included. The results should be:

Name Number TotalSales Auction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys

How do I prevent the unsolds (zeros) being counted?
Thanks in advance,

John Furphy

[David Portas]

Assuming you also want to exclude NULL saleprices (if any):

SELECT name, COUNT(NULLIF(saleprice,0)) AS number,
SUM(saleprice) AS totalsales, auction
FROM MyTable
GROUP BY name, auction
HAVING name='Dowling' AND SUM(saleprice)>0;

--
David Portas
------------
Please reply only to the newsgroup
--

[John Gilson]

"John Furphy" <jo********@a1.com.au> wrote in message
news:4c**************************@posting.google.c om...

Could someone assist with getting the count function working correctly
in this example please. I know the count function will return all rows
that do not have null values, but in this case I want to count all the
rows except those with a zero sale price, (which are unsold).

The table shows works offered for sale by an artist, with a positive
figure under SalePrice indicating a sale, and I want to count the
number sold by each auction house, and sum the sale price by auction
house. The table is as follows:

Name SalePrice Auction
Dowling 12000 Christies
Dowling 0 Christies
Dowling 10000 Christies
Dowling 0 Christies
Dowling 0 Christies
Dowling 6000 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys
Dowling 8000 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys
Dowling 0 Sothebys

When I run this query:

SELECT MyTable.Name, Count(MyTable.Name) AS [Number],
Sum(MyTable.SalePrice) AS TotalSales, MyTable.Auction
FROM MyTable
GROUP BY MyTable.Name, MyTable.Auction
HAVING (((MyTable.Name)="Dowling") AND ((Sum(MyTable.SalePrice))>0));

The results are:

Name Number TotalSales Auction
Dowling 5 22000 Christies
Dowling 7 14000 Sothebys

The TotalSales is correct, but the Number (Count) is incorrect, as the
rows with zero were also included. The results should be:

Name Number TotalSales Auction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys

How do I prevent the unsolds (zeros) being counted?
Thanks in advance,

John Furphy

SELECT "Name",
COUNT(*) AS Number,
SUM(SalesPrice) AS TotalSales,
Auction
FROM MyTable
WHERE SalesPrice > 0
GROUP BY "Name", Auction

Regards,
jag