470,591 Members | 2,074 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,591 developers. It's quick & easy.

Basic SQL question...

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...
Jul 23 '05 #1
6 1190
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;

Jul 23 '05 #2

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

Jul 23 '05 #3
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
Jul 23 '05 #4
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.

Jul 23 '05 #5
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;

Jul 23 '05 #6
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

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by pauldepstein | last post: by
9 posts views Thread by Malcolm | last post: by
4 posts views Thread by Ramesh | last post: by
5 posts views Thread by Aussie Rules | last post: by
4 posts views Thread by MikeB | last post: by
3 posts views Thread by Scott Stark | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.