Select count - should return zero if none.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select count - should return zero if none.

Author

Topic

ThHv
Starting Member

3 Posts

Posted - 2002-02-19 : 07:09:01

Hello

I have a query that counts the orders per month, and group by company. But how do I get a result that includes the company-name even if orders that month = zero ?

Something like:


Company orders January 2002:

Company	   orders total
-------------------------------
IBM	     6
MS	     8
HP	     4
Compaq	     0
Dell	     0

Regards
Thomas

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-02-19 : 07:17:58

Why don't you show us what your query is ? Then we can help you a little. But I suspect you can do what you want with an isNull(arg, 0) in there somewhere.

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-19 : 07:18:04

How are your tables set up? Not having your table structures makes it very hard to figure out an answer.

If you have two tables, one for company information, the other for orders (like Northwind), then something like this should work:

SELECT C.Company, Count(O.CustID) AS OrderTotal
FROM Companies C LEFT JOIN Orders O ON (C.CustID=O.CustID)
GROUP BY C.Company

The LEFT JOIN retains all rows from the Companies table; if there are no orders for a company the columns from Orders will be NULL. This is also why you can't use Count(*) because it would return a 1 instead of a 0, you need to Count() on a column in the Orders table.

ThHv
Starting Member

3 Posts

Posted - 2002-02-19 : 08:31:32

Thank you for your kind and fast answers.

Well, sorry but I'm prohibited by company policy/security to provide you with any company data.

This is still pseudo, but it might help

The table is a view named "View_Event" and it contains these three columns:


   Eventname      Logdtime     Company   
----------------------------------------------------

And my query:


Select Company, count(Company) as Total
From View_Event
Where Eventname Not Like 'Joke%' And datepart(mm,logdtime)= 1 And datepart(yyyy,logdtime)= 2002
Group by company
Order by Total desc

Thanks again
Thomas

Edited by - ThHv on 02/19/2002 08:32:11

Edited by - ThHv on 02/19/2002 08:33:47

Nazim
A custom title

1408 Posts

Posted - 2002-02-19 : 08:41:22

So, you dont have a seperate table which holds all company information.

in that case this query will should give you the list of companys with zero count too(but there should be atleast one entry in the table irrespective of the filter condition)

Select c.Company, count(e.Company) as TotalFrom View_EventWhere E
right join
(Select distinct Company From View_EventWhere ) C
on e.company=c.Company
where
Eventname Not Like 'Joke%' And datepart(mm,logdtime)= 1 And datepart(yyyy,logdtime)= 2002
Group by C.company
Order by Total desc

--------------------------------------------------------------

Edited by - Nazim on 02/19/2002 08:42:41

ThHv
Starting Member

3 Posts

Posted - 2002-02-20 : 04:49:28

It looks like a solution, but could you please revise the query.

Nazim
A custom title

1408 Posts

Posted - 2002-02-20 : 07:37:50

Revise on what conditions, if you point out the omissions in the query then we should be able to refine it. but i will suggest you to hold all the company's information in a seperate table and go for Rob's solution.

quote:

It looks like a solution, but could you please revise the query.

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources