using to count() to get what I want

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)

using to count() to get what I want

Author

Topic

marko_one
Starting Member

6 Posts

Posted - 2004-08-31 : 05:51:41

Hi All, I have 2 tables, Orders and OrderStatus. The OrderStatus table is a reference table, where ID 1 = 'New', 2 = 'Locked', 3 = 'Processing', 4 = 'Complete'. The Orders table has a foreign key OrderStatusID. The customer table is linked to orders table by a foreign key in Orders table (CustomerID). What I need to do is get a count of all order status's. But what I also need is a count of 0 if none of the orders have status of say 2 (Locked), this will give a result set such as


StatusID     Counter
1            12
2            0
3            5
4            0

I have tried to do something on this, but if I don't have any records with a statusID of 2, then I will just get a result like :


StatusID     Counter
1            12
3            5

this is the SQL I have used :


select     
	dbo.OrderStatus.ID as StatusID, 
	Count(dbo.OrderStatus.ID) as Counter
from 
	dbo.Orders join dbo.OrderStatus on dbo.Orders.OrderStatusID = dbo.OrderStatus.ID
group by dbo.OrderStatus.ID

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 05:59:48

Try

select
dbo.OrderStatus.ID as StatusID,
Count(dbo.OrderStatus.ID) as Counter
from
dbo.Orders join dbo.OrderStatus on dbo.Orders.OrderStatusID = dbo.OrderStatus.ID
group by ALL dbo.OrderStatus.ID

-------
Moo. :)

marko_one
Starting Member

6 Posts

Posted - 2004-08-31 : 06:23:09

Many thanks mr_mist,

Unfortunately it still gives me the same result :(, no zeros

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 06:45:18

would this work:

select
dbo.OrderStatus.ID as StatusID,
isnull(Count(dbo.OrderStatus.ID), 0) as Counter
from
dbo.Orders
left join dbo.OrderStatus on dbo.Orders.OrderStatusID = dbo.OrderStatus.ID
group by ALL dbo.OrderStatus.ID

Go with the flow & have fun! Else fight the flow :)

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 06:47:17

Aye, that should work, I didn't notice the join in there.

-------
Moo. :)

marko_one
Starting Member

6 Posts

Posted - 2004-08-31 : 07:17:14

Thanks so much for the answer you gave. It has improved my results, and it does indeed give a 0 where required, but now the StatusID column gives a NULL value where the counter has a value of 0. Such as

StatusID Counter
1 12
NULL 0
3 5
NULL 0

As you may probably realise I am not too savvy with T-SQL, and I imagine this is a simple task to fix, but I have no idea how to do it - can you help me?

Thanks

Marko.

marko_one
Starting Member

6 Posts

Posted - 2004-08-31 : 07:18:42

sorry, didnt wrap in code blocks


StatusID Counter
1        12
NULL     0
3        5
NULL     0

As you may probably realise I am not too savvy with T-SQL, and I imagine this is a simple task to fix, but I have no idea how to do it - can you help me?

Thanks

Marko.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 07:25:27

how about:


select * from
(
select 
	dbo.OrderStatus.ID as StatusID, 
	Count(dbo.OrderStatus.ID) as Counter
from 	dbo.Orders inner join dbo.OrderStatus on dbo.Orders.OrderStatusID = dbo.OrderStatus.ID
group by ALL dbo.OrderStatus.ID
union all
select 	dbo.OrderStatus.ID, 0
from 	dbo.OrderStatus
where 	not exists (select distinct dbo.Orders.OrderStatusID from dbo.Orders where dbo.Orders.OrderStatusID is not null)
) t
order by StatusID

EDIT: i made a mistake so here is the correct version i meant

Go with the flow & have fun! Else fight the flow :)

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-31 : 07:42:53

What about



create table orders (orderstatus int)
create table orderstatus (orderstatus int)

select * from orders

insert into orders values (1)
insert into orders values (2)
insert into orders values (2)
insert into orders values (3)
insert into orders values (3)
insert into orders values (3)
insert into orders values (4)
insert into orders values (4)
insert into orders values (4)
insert into orders values (4)

Insert into orderstatus values (1)
Insert into orderstatus values (2)
Insert into orderstatus values (3)
Insert into orderstatus values (4)
Insert into orderstatus values (5)

SELECT orderstatus.orderstatus, count(orders.orderstatus) from orderstatus 
left outer join orders on  orders.orderstatus = orderstatus.orderstatus
group by orderstatus.orderstatus

-------
Moo. :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 08:13:27

from the original post...


select     
	dbo.OrderStatus.ID as StatusID, 
	(Select Count(1) From dbo.Orders Where dbo.Orders.OrderStatusID = dbo.OrderStatus.ID) as Counter
from dbo.OrderStatus

Corey

marko_one
Starting Member

6 Posts

Posted - 2004-08-31 : 08:32:02

Thank you all so much.

spirit1 - Your code didn't quite do it, nut running an adppted version of mr_mist's select statement did the trick

Thanks again

Marko.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources