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:
NameSalePriceAuction
Dowling12000Christies
Dowling 0Christies
Dowling10000Christies
Dowling 0Christies
Dowling 0Christies
Dowling 6000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 8000Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
Dowling 0Sothebys
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:
NameNumberTotalSalesAuction
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:
NameNumberTotalSalesAuction
Dowling 2 22000 Christies
Dowling 2 14000 Sothebys
How do I prevent the unsolds (zeros) being counted?
Thanks in advance,
John FurphyAssuming 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 Furphy" <johnfurphy@.a1.com.au> wrote in message
news:4ce579e8.0312010326.115691db@.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
