Basic SQL question...

[Blue Streak]

Hello, folks.

I have the following query that I run that pulls up the count of the number of orders of a product.

SELECT Product, Count(Product) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

This query is fine but the COUNT() function only coughs-up the non-zero results. How can I get this query to cough-up the zero counts as well for all products listed in the Product table?

TIA...

[louis]

Count(Product) will count non-null columns. To count null columns
either use ISNULL function or count(*).

SELECT Product, Count(isnull(Product,0)) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

SELECT Product, Count(*) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

[dbmonitor]

Blue Streak wrote:

Hello, folks.

I have the following query that I run that pulls up the count of the number of orders of a product.
SELECT Product, Count(Product) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

This query is fine but the COUNT() function only coughs-up the non-zero results. How can I get this query to cough-up the zero counts
as well for all products listed in the Product table?
TIA...

SELECT Product,
(SELECT COUNT(*)
FROM Orders_View
WHERE Orders_View.Product = Product.Product
AND Year([DateOpened])=2004
AND Month([DateOpened])=10) AS ProductSales
FROM Product

--
David Rowland
http://dbmonitor.tripod.com

[Erland Sommarskog]

Blue Streak (an*******@msn.com) writes:

I have the following query that I run that pulls up the count of the
number of orders of a product.

SELECT Product, Count(Product) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

This query is fine but the COUNT() function only coughs-up the non-zero
results. How can I get this query to cough-up the zero counts as well
for all products listed in the Product table?

It is always adviceable for this type of question to post:

1) CREATE TABLE statement for your tables (and in this case also the view)
2) INSERT statements with sample data.
3) The desired output given the sample data.

That permits anyone answering your question to easily cut and paste into
to Query Analyzer and develop a tested solution.

The solution suggested by David Rowland should give you the desired
result, but it may not be that performant.

An alternative is to explorr GROUP BY ALL, but with out the table and
view definitions, I can't test.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

You might want work on better names for data elements. A name should
tell us what the data element is, and not how it is stored (i.e. VIEW);
it ought to tell us the attribute involved (product what? Weight? Size?
Id?)

Since you did not bother with DDL, I am going to assume that there is
Products table somewhere and that this will work.

SELECT P.product_id, COUNT(O.product_id) AS total
FROM Products AS P
LEFT OUTER JOIN
Orders AS O
ON P.product_id = O.product_id
WHERE O.date_opened BETWEEN '2004-10-01 00:00:00.000'
AND '2004-10-31 23:59:59.99'
GROUP BY I.product_id;

Hint about temporal data: do not split it up into pieces to do numeric
or string operations on the conversions. Think of it as a data type in
its own right, with its own operators. This is not just a matter of the
extra overhead, but the way you approach a problem.

[Blue Streak]

10Q very much!

"louis" <lo************@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Count(Product) will count non-null columns. To count null columns
either use ISNULL function or count(*).

SELECT Product, Count(isnull(Product,0)) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

SELECT Product, Count(*) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

[Blue Streak]

Mercy Buckets!!!!

This one worked!
"dbmonitor" <db***************@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Blue Streak wrote:

Hello, folks.

I have the following query that I run that pulls up the count of the

number of orders of a product.

SELECT Product, Count(Product) AS Total
FROM Orders_View
GROUP BY Product, Year([DateOpened]), Month([DateOpened])
HAVING (((Year([DateOpened]))=2004) AND ((Month([DateOpened]))=10))
ORDER BY Product;

This query is fine but the COUNT() function only coughs-up the

non-zero results. How can I get this query to cough-up the zero counts
as well for all products listed in the Product table?

TIA...

SELECT Product,
(SELECT COUNT(*)
FROM Orders_View
WHERE Orders_View.Product = Product.Product
AND Year([DateOpened])=2004
AND Month([DateOpened])=10) AS ProductSales
FROM Product

--
David Rowland
http://dbmonitor.tripod.com